Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Using the CROSS FILTER function in Power BI Desktop |
---|
Learn about the perils of changing the cross filter direction to "Both", then discover why the CROSS FILTER function is better! |
In this blog
This blog shows how and why you might use the CROSSFILTER function in DAX to change the join type used for relationships.
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.
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!
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!
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, or to learn more about hwo to write measures in DAX consider booking a place on our two-day DAX course.
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.