BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Sam Lowrie on 13 June 2018
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Using the CROSS FILTER function in Power BI Desktop
This blog shows how and why you might use the CROSSFILTER function in DAX to change the join type used for relationships.
Setting the scene
I have a report which lists out the products my (fictional) company sells and where they were sold. For each sale I have the shopping centre where it occurred:
Obviously a single product can have been sold in many different centres.
For each product, I would like to know how many centres it is being sold in:
I'm not saying it's impossible, but it does seem unlikely that all products are sold in all stores.
So what is happening? The answer lies within the relationship window:
Filtering occurs along the direction of the relationship arrow. Looking at my relationships there appears to be a clash....
Each purchase has listed next to it the product sold (ProductId) and the location it was sold at (CentreId). This allows a single product to show only relevant purchases, but that is where the filtering ends.
One solution - changing the nature of the relationship
To change this:
Double click the join between the Purchase and Centre tables.
Changing the Cross filter direction to Both will allow the Purchase table (and therefore the Product table) to filter the Centre table. This should make our count of centres for each product correct:
Looks great - but with great Power BI comes great responsibility.
Create two slicers, one containing the ProductNames and one containing the CentreNames:
Looking good with no filters.
What happens when I try filtering my table using the ProductName slicer?
Some of the CentreNames have vanished! They are now showing only centres which sold the product Bob.
I wanted to count the centres that sold particular products, but I didn't want to affect all future calculations and visuals!
A better solution? Using a DAX measure to change cross-filtering temporarily
In the relationship view switch the filtering back to Single:
Filtering by centre should change which products you see, but changing the products shouldn't impact on the centres displayed: ie the relationship should be one-way only, from parent (centre) to child (purchase).
We can do this by creating a measure which mimics the effects of this change:
You can use the CALCULATE function to change the filter context for a calculation (such as the relationship between two tables).
CROSSFILTER takes in the columns from each table used to join the tables together, and lets the users indicate the direction of filtering allowed. Just like we did previously in the relationship view, but this time only for this one calculation!
The measure is showing the number of stores for each product as if a Both relationship existed, while the count shows the relationship doesn't exist!
Checking the result
Has this fixed our slicers from being affected?
Yes it has! All centre names are showing, not just those Bob is sold in.
The measure looks the same whether it is one table relationship being changed or ten. Here is an example for changing the relationship filtering between 3 tables:
This lets me work out how many regions each Product is sold in. The Region table is 3 relationships away!
To see these relationship measures in action, click here.