In this Power bi tutorial, we will see power bi slicer multiple columns. And also we will see the below points:
- How to create power bi slicer multiple columns
- How power bi slicer format multiple columns
- How power bi slicer search multiple column
- How power bi one slicer for multiple date columns
- Limitations for power bi slicer multiple columns
How to create power bi slicer multiple columns
Here we will see how to create a power bi slicer with multiple columns.
When you want to filter multiple fields or columns in a single slicer in power bi, you do so by building a hierarchy slicer.
Here I have taken the Product hierarchy which contains the product name column and the category name column, and also I selected the segment column to create a slicer.
Now in the power bi slicer when you expand the parent column i.e. product name, you can see the category column and when you expand the category column you can see the segment column.
When you select the column product name(parent) then it will select other columns (Child).
- Open Power bi desktop. Load the data by using get data.
- Click on the slicer from the visualization
- In the Field, drag and drop the product name hierarchy (product name and category) and segment columns from the field pane.
- When you add multiple column to the power bi slicer, by default it display the arrow next to the items, click on that to expand, to show the items in the next level.
- Now you can select the multiple columns, i.e. i have selected Product name( parent) and then select category column.
- When you expand the category column you can see the two options for consumer and corporate
- Here you can select the both corporate and consumer or you can select any one of these.
- I have created a table visuals which contains product name, category, and segement to see the result.
- In the below screenshot you can see the power bi slicer multiple columns
Read Power Bi slicer multiple selections
How power bi slicer format multiple columns
Here we will see how power bi slicer formats multiple columns in power bi desktop.
We will see how to format the style, title, expand/ collapse icon, and indentation of the power bi slicer.
Format the slicer style
- You can change the style of the slicer to a single selected slicer.
- And also you can swap between list and dropdown.
Format the title
- We can edit the title of any slicer and it is especially useful for hierarchy slicer.
- To change the title of the slicer, click on the formattting section in the visualization pane.
- Expand the slicer header, under Title text you can change the title.
Once you change the title you can see the title on the power bi slicer.
Format the expand and collapse icon in the slicer
- Power bi hierarchy slicer have some other formatting option
- We can change the expand/ collapse icon from the default arrow to a caret or plus/minus sign.
- To change the expand/collapse icon, click on the Formatting section in the visualization pane.
- Expand the items, then scroll down to Expand and collapse icon, from the dropdown select the style of the icon.
- In the below screenshot, you can see the power bi slicer expand/ collapse icon change from Chevron to plus/minus.
Format the indentation of the slicer
- If the space is tight on your power bi report and you may want to reduce the amount you indent the child item in power bi.
- In that case you can increase or decrease the indentation, by default it is 15.
- To change the indentation of item in power bi slicer, click on the Formatting section in the visualization pane.
- Then expand the items section, and you can change the stepped layout indentation.
Read How to set default value in Power BI Slicer
Power bi slicer search multiple columns
Here we will see how the power bi slicer searches multiple columns.
- In power bi default slicer, the search functionality is not there.
- For this you can use the custom visual i.e. Text Filter, first you concatenated the multiple columns into one, then use the text filter visual for the search.
Power bi date slicer for multiple columns
Here we will see how power bi one slicer having multiple date columns in the power bi desktop.
We will create a power bi slicer and we will add the order date of the product and ship date of the product. In the slicer, the date will show in hierarchical order.
When you select a date in the slicer it will filter the data accordingly.
For example, 3 Jan 2014 is the order date and the ship date is 7 Jan 2014, when you select this date which product is delivered it will show.
- Select the slicer from the visualization in power bi desktop.
- In the Field, drag and drop the order date and ship date columns from the field pane
- When you expand the order date, you can see the ship date.
- When we select one date it will filter and show which product is ordered in the table visual (i have create table visual which contanins order date and product name).
- In the below screen shot you can see the power bi one slicer for multiple date column.
Limitations of power bi slicer multiple columns
Here we will see limitations for power bi slicer multiple columns.
The limitations of power bi slicer are:
- In the power bi when you are using live connection to SQL server Analysis service (SSAS) tabular model and the property hide members in the hierarchy is set to hide blank memebers.
- Whereas Power bi doesnot hide the blank memebers. This creates a ragged hirearchy in power bi slicer.
- In power bi slicer a ragged hirearchy is also created when there are blank values for a category at any hierarchy level.
- In power bi for tabular model, this feature requires SSAS 2017 or newer.
- In power bi for multi-dimensional model, this feature require SSAS 2019 CU5 or newer with SuperDAXMD enabled.
You may like the following Power BI tutorials:
- Power bi slicer panel – How to use
- Power BI Matrix
- Power BI Filter vs Slicer
- How to use Power bi maps
- Microsoft Power BI Combo Chart
- Power bi slicer checkbox
In this power bi tutorial, we learned power bi slicer multiple columns. And also we discussed the below points:
- Power bi slicer multiple columns
- Power bi slicer format multiple columns
- Power bi slicer search multiple columns
- Power bi one slicer for multiple date columns
- Limitations of power bi slicer multiple columns
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com
i have multiple column like jan, feb, mar… with value. and multiple row like salary, rent, other expenses… i want to visualize latest month column value of salary, rent, other expenses…… and i want slicer of month. please tell me how to resolve it.
If we have table with sales, country and date columns. we want slicer that contain date and country data. if we click on country then only shows as per country and if we click on date then sales need to show as per date.