Power BI – Cascading Slicers
In this post I will look at filtering one slicer based a selection in another slicer a.k.a. cascading slicers. 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
- Clear all Slicers Button
- 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 is now a cascading slicer and will filter based on shop selected.
Conclusion to Cascading Slicers
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.
More Power BI Posts
- Conditional Formatting Update
- Data Refresh Date
- Using Inactive Relationships in a Measure
- DAX CrossFilter Function
- COALESCE Function to Remove Blanks
- Personalize Visuals
- Gradient Legends
- Endorse a Dataset as Promoted or Certified
- Q&A Synonyms Update
- Import Text Using Examples
- Paginated Report Resources
- Refreshing Datasets Automatically with Power BI Dataflows
- Charticulator
- Dataverse Connector – July 2022 Update
- Dataverse Choice Columns
- Switch Dataverse Tenancy
- Connecting to Google Analytics
- Take Over a Dataset
- Export Data from Power BI Visuals
- Embed a Paginated Report
- Using SQL on Dataverse for Power BI
- Power Platform Solution and Power BI Series
- Creating a Custom Smart Narrative
- Power Automate Button in a Power BI Report
Power BI Series
No credit given to SQLBI who invented the INT NOT ISEMPTY pattern 🙁
Totally valid point I will correct!