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

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:

• What is a Relationship?
• What is the Cardinality of the Relationship?
• What is the Direction of the Relationship?
• Inactive relationship and what to do about it?

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.

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;

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

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;

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.

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.

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;

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

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.

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:

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.