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!

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:

Centres Measure Cross Filtering

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:

Cross filtering

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:

Relationships are hard

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:

Cross filter direction

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:

Power BI Desktop cross filtering

Looks great - but with great Power BI comes great responsibility.

 

Create two slicers, one containing the ProductNames and one containing the CentreNames:

Slicers filtering

Looking good with no filters.

What happens when I try filtering my table using the ProductName slicer?

Slicers cross filtering

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:

Relationship view cross filter direction

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:

Calculate

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!

CROSSFILTER DAX

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?

CROSSFILTER DAX Measures

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:

CROSSFITLER multiple 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.

This blog has 0 threads Add post