DAX CrossFilter Function in Power BI: Write the Formula both-directional, but keep the relationship single-directional

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
DAX CrossFilter function in Power BI

If you are familiar with relationships in Power BI, you know that there are scenarios that you may need to change the direction of the relationship to a both-directional. A both-directional relationship comes at a cost, which is mainly performance and ambiguity of the model. There is a way to write a calculation in a both-directional way, but keep the relationship still single direction. This would help with the performance too, because the performance impact will only happen when using this measure. In this article and video I explain how you can do that.

Video

Understand the relationship

Before I talk about the function itself, I want to emphasize the need to understand how the relationship in Power BI works. I have written many blog articles (and videos) about this subject, here are some of those:

Amongst the articles above, the one that is most related to this post is:

The model I am using in this sample has the relationships as below.

Sample model with multiple fact tables

I have chosen the model above to show some samples of filtering data of a table by another table. As you can see, we have three fact tables in the above model (highlighted), and all the relationships are single-directional.

CrossFilter DAX Function

CrossFilter function is neither tabular nor scalar function, It is a specific type of function that changes the direction of a relationship. This function cannot be used just by itself, it has to be used as the filter part of other functions such as Calculate. Here is how the function works;

CROSSFILTER(<columnName1>, <columnName2>, <direction>) 
  • Column1: The column on one side of the relationship
  • Column 2: the column on the other side of the relationship
  • direction
    • None: no filtering
    • Both; filtering will propagate both ways
    • Oneway; filter propagates from one side of the relationship to the many side
    • Oneway_LeftFiltersRight; in a one-to-one relationship, the left table filters the right table
    • Oneway_RightFiltersLeft; in a one-to-one relationship, the right table filters the left table

The best way to understand a function, is to learn it through an example, so let’s check a couple of scenarios.

Changing the direction to both-directional through DAX expressions

Let’s assume that a reporting requirement is to have a list of customers and the sales amount related to them (coming from FactInternetSales) as below;

sample table visual

This works absolutely fine, because the FactInternetSales table gets filtered by the DimCustomer table;

the single-directional relationship between the two tables

Now, an additional requirement is to also have sum of Reseller sales for all the products that every customer have purchased. If we bring Sum of Reseller Sales, it won’t work;

the slicing and dicing from the customer table to FactResellerSales table doesn’t work

This is because the FactResellerSales, doesn’t get filtered from the DimCustomer side, It only gets filtered through the DimProduct, and the DimProduct is not getting filtered from the DimCustomer too.

the direction of filtering doesn’t support the requirement

Now, one method to solve this is to make the relationship between the FactInternetSales and the DimProduct both-directional. However, that method will keep this relationship both-directional always. Another method, is to keep the relationship single-directional as is. But just for this requirement, create a measure that uses a both-directional relationship, and that is when the CrossFilter function comes to help.

You can create a measure as below;

Reseller Sales of the products that this customer purchased = 
CALCULATE(
    SUM(FactResellerSales[Reseller Sales]),
    CROSSFILTER(
        DimProduct[ProductKey],
        FactInternetSales[ProductKey],
        Both)
)

In the expression above, the CrossFilter changes the direction of the existing relationship between the DimProduct[ProductKey] and FactInternetSales[ProductKey] to both-directional.

CrossFilter changes the direction of the relationship

The result is working perfectly fine as you can see in the screenshot above. Please note that there might be other ways to calculate the same outcome, however, I used the CrossFilter here to show how this function can be used.

Multiple Relationships

To understand how it works if you have multiple relationship, let’s discuss another requirement; Let’s say, we want to see the Sum of SalesQuote (from FactSalesQuota) table for all the employees (from DimEmployee) that have sold products (from FactResellerSales) that each customer have purchased (from FactInternetSales).

For a requirement as above, we need all tables on deck. If we use sum of the SalesQuota from the FactSalesQuota table, it is not going to work;

the current relationship doesn’t support the requirement

This time, we have two relationships that are not supporting the direction needed for the requirement.

two relationships need to be both-directional

The two red relationships above need to be both-directional to get the result, our calculation can be like below;

SalesQuota of Employees who have sold the products that this customer purchased = 
CALCULATE(
    SUM(FactSalesQuota[SalesAmountQuota]),
    CROSSFILTER(
        DimProduct[ProductKey],
        FactInternetSales[ProductKey],
        Both),
    CROSSFILTER(
        DimEmployee[EmployeeKey],
        FactResellerSales[EmployeeKey],
        Both)
)

As you can see, we can use multiple CrossFilters to change the direction of multiple relationships.

changing the direction of multiple relationships using DAX in Power BI

Limitations, considerations, and recommendations

Before you start doing calculations this way, I have to explain a few things:

  1. Always consider a good modeling to cover the requirement without the need of both-directional relationship.
  2. If you have to create a both-directional relationship regardless of the best practice modeling, then use the CrossFilter approach as much as possible, it can be better for the performance. The performance impact would be only when you use that particular measure in a report page, not always.
  3. The CrossFilter requires an existing relationship between the two tables.

Summary

In Summary, CrossFilter is a useful function to change the direction of the relationship. You can use this method instead of changing the direction in the relationship itself. However, always consider best practices of the modeling beforehand. In this article you’ve learned a couple of examples showing how this function can be used.

Download Sample Power BI File

Download the sample Power BI report here:

    Enter Your Email to download the file (required)

    FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
    Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
    Reza Rad
    Trainer, Consultant, Mentor
    Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
    His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
    He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
    He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
    Reza’s passion is to help you find the best data solution, he is Data enthusiast.

    Leave a Reply