Power BI – Cascading Slicers

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
• Sync Slicers
• Relative Date Slicer

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/

https://www.sqlbi.com/articles/check-empty-table-condition-with-dax/

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.