Power BI Cross Filter Direction
- 6th January 2020
- Debbie Edwards
Your Power BI Data Model contains multiple tables that need to be joined in the Relationship Pane.
Cardinality is well understood (1 to many, many to many etc) But what does the Cross filter direction mean?
Example using Azure SQL Database Adventureworks Data Warehouse
You can Create a SQL data warehouse in Azure.
Note that Sample Source has been chosen which opens up the AdventureWorksDW option
In Power BI Get Data and Azure Data Warehouse
Please note. If you are using the Warehouse to do a quick demo against, always remember to either Delete the Warehouse or Pause the Warehouse unless you want to create unnecessary costs within Azure
Login to your Server in SQL Server Management Studio with the Username and Password set up in Azure
Note, if there are issues you may need to go to the SQL Server in Azure and add your IP address to the firewall configuration.
Choose the Dims and Fact table(s) and then click OK
Then Close and Apply (Power Query Editor)
Next, In Power BI Desktop go to the relationships pane
The relationships are already set up with Cross filtering set to single.
Create a Report to test the Filtering
Add a Slicer and then go to DimProductCategory and Choose EnglishProductCategoryName for the Slicer Value
Add Slicer 2 and then go to DimProduct and Choose EnglishProductName for the Slicer Value
Now you can select a Product Category and only those Products in that Product category will be shown
In this example I have chosen Accessories and EnglishProductName only displays Accessories
If you unselect the Category ALL products are then available in Slicer 2
If I then, for example, Choose Cable Lock from the list, Nothing happens to slicer 1. Everything is still visible
How do you enable cross filtering?
Enable Cross Filtering
Go back to the Relationships pane and click on Manage Relationshipsedit the Relationship between DimProduct and DimProductSubCategory
Set the Cross Filter Direction to Both (And tick apply security filter in both directions)
You can now select an English Product Name and Slicer 1 will only show the Category that the Product Name Belongs to
Cross Filtering, in the case of Slicers allows you to not only move top down but also from bottom up of the hierarchy
This is great when the reports created need Multiple slicers to select the data for the Reports