Day: September 15, 2019

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

• DimProduct
• DimProductCategory
• DimProductSubCategory
• FactResellerSales

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

Conclusion

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