This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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 11 June 2018

In this blog

Setting the scene
One solution - changing the nature of the relationship
A better solution? Using a DAX measure to change cross-filtering temporarily
Checking the result

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:

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:

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, 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:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Using the CROSS FILTER function in Power BI Desktop

Setting the scene

One solution - changing the nature of the relationship

A better solution? Using a DAX measure to change cross-filtering temporarily

Checking the result

Head office

London

Manchester