In this post I will look at filtering one slicer based a selection in another slicer. June 2019 Power BI Update included an update to allow a slicer to be filtered based on a measure, which means that you no longer need to use bi-directional relationships.
Slicer Series
This series will cover different topics regarding slicers.
- Slicers Introduction
- Resetting Slicers with a Bookmark Button
- Cascading Slicers
- Hierarchy Slicer
- Syncing Slicers
Problem Overview
My simple example is a report showing sales of a product in different shops. The example report contains three tables, Products, Shops and Sales and I’ve related the tables as shown below.
Note the relationships show using arrows that Products can filter Sales and Shops can also filter Sales, and Sales cannot filter either Shops or Products. So I create a report with 2 slicers, a table showing product sales and a card showing total sales.
If I use the Shop Name slicer to filter to Daisy Delights the card and table filter to only show the sales of the 5 products that Daisy Delights sell. BUT the slicer of products does not get filtered.
Creating the Measure
So we want to create a measure to indicate if there are sales records. My initial reaction was to create a boolean (True / False) measure using ISEMPTY on the Sales table something similar to
Puzzled I went looking, and of course the best site SQLBI gave me the answer to use. the links I used were.
https://www.sqlbi.com/articles/syncing-slicers-in-power-bi/
That measure would be True if Sales records exist and False if no Sales records exist. Sadly this measure doesn’t work as a slicer filter so we need to convert the True and False to numbers 1 and 0, the fastest way to do this INT function. So the above measure becomes
Filtering the Slicer
We then need to add the measure to the slicer filters. I select the slicer and drag the measure into the filter pane. I then set the operator to is and the value to 1. Then I click Apply Filter and the list of products is now filtered to only 5 products. The Product slicer will now filter based on shop selected.
Conclusion
Even though I would prefer to use the boolean filter as that makes logical sense to me, this is a cool update and it reduces those nasty bi-directional filters.
Reference:
Brown-Graham, L. (2020). Power BI – Cascading Slicers. Available at: https://hatfullofdata.blog/power-bi-cascading-slicers/ [Accessed: 9th April 2020].
