In this power bi tutorial, Power bi slicer filter another slicer. And also we will see the below points:
- What is slicer in power bi?
- How to create power bi slicer filter another slicer
- How to create power bi slicer filter other slicers using DAX.
- Power bi slicer not filtering other slicer
What is a slicer in Power bi?
- Power bi slicer are a type of canvas visual filter.
- The slicer in power bi enable a user to sort and filter packed report and view only the information they want.
- The power bi slicer are present as a visual on the report and let a user select values as they are analyzing the report.
How to create power bi slicer filter another slicer
Here we will see how to create a power bi slicer filter another slicer in power bi desktop using sample data.
We will create two Power Bi slicers in one we will add a customer name and in the other, we will add a product name. When we click on one slicer it will filter the other slicer.
- Open power bi desktop. Load the data using get data.
- Select a slicer from the visualization pane in power bi desktop.
- In the value field, drag and drop the FullName from the field pane.
- Select one more slicer, and drag and drop EnglishProductName from the field pane.
- After adding two slicer, when you click on one slicer it will not filter the other slicer because we need to have both direction to get it working.
- Go to the Model Tab in Power bi desktop, you can see the single direction relationship.
- To change the direction of reltionship to both direction to get it working.
- Double click on the relationship line and it will open the edit relationship dialog box.
- Under Cross filter direction select both. Click on OK.
In the below screenshot, you can see both directions’ relationship.
- Click on the report tab in power bi desktop
- From the full name slicer, Select the name, you can see the other slicer also get filter.
How to create power bi slicer filter other slicers using DAX
Here we will see how to create a power bi slicer filter for other slicers using DAX in power bi desktop.
One slicer filters other slicers from the different tables without changing the relationship between the two tables.
- Open power bi desktop and then Load the data using get data.
- Select a slicer from the visualization pane in power bi desktop.
- In the value field, drag and drop the full name from the field pane.
- Select one more slicer, and drag and drop EnglishProductName from the field pane.
- Now we will create an measure, which we will use to filter slicer.
- Click on the New measure from the Ribbon in power bi desktop.
- Write the Measure. The measure is:
Mutual filter measure = CALCULATE(SUM(FactInternetSales[SalesAmount]))
- Select the EnglishProductName slicer visual.
- Then In Filter pane, under filter on this visual, drag and drop the measure to the ‘add data field here’.
- Under Show item when the value is, click on the drop-down and select is greater than.
- Then in the blank box write 1.
- Click on Apply filter.
- Click on the name in FullName slicer, and it will filter the other slicer.
- In the below screenshot you can see the power bi slicer filter other slicers using DAX.
Power bi slicer not filtering other slicer
Here we will see the power bi slicer(date) not filtering other slicers (date) in the power bi desktop.
When you click on one date slicer it will not filter another slicer that also dates column.
If you are using the date column from two different tables in slicers, then correct the relationship between the two tables.
If you are using the date column from the single table then follow the below steps.
We will see the solution of how one date slicer does not filter another date slicer.
- Open power bi desktop
- Load the data using get data.
- Select the slicer from the visualization drag and drop the fiscal year from the field pane.
- Select another slicer drag and drop FullDateAlternateKey from the field pane.
- Select another slicer drag and drop CalendarYear from the field pane.
Select the Format tab from the ribbon. Click on the Edit interactions.
- Highlight one slicer by disabling the none icon and click on Filter icon.
- You can filter the date slicer by using another date slicer.
- Click on the year and you can see the highlighted slicer get filter.
You may like the following Power BI tutorials:
- Power bi treemap – How to use
- Power BI Slicer Search Box
- Power BI Slicer Buttons
- How to use Power bi ribbon chart
- Power BI Q&A – How to use with examples
- Power bi slicer contains
In this power bi tutorial, we learned power bi slicer filters other slicers. And also we learned the below points:
- What is slicer in power bi?
- How to create power bi slicer filter another slicer
- How to create power bi slicer filter other slicers using DAX.
- Power bi slicer not filtering other slicer
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
Hello,
Thanks for the article. I like the idea of using a measure to filter one slicer by another.
I have tried this with my slicers, unfortunately, with no success. I don’t understand the measure and how it is connected to the slicers. How do you determine which column is used? Do you then filter just one of the slicers by this measure?
In my model, I have two categories and I want the choice of one to be filtered by the choice made in the other. So basically, same principle as when you filter in the query.
Thanks for your reply.
Jana
Perfect! Thanks a lot