Power BI DAX’s CROSSFILTER Function

The function you didn’t know you needed in DAX

In this post, I am going to talk about a function that you’ve probably never thought about using when you got stuck while visualizing a measure that returned the same numbers and you couldn’t solve the problem with the usual DAX functions. I think you already know which usual DAX functions I am talking about here. You tried using CALCULATE, FILTER, ALL, ALLSELECTED, and changing all relationships within your data model but nothing clicked. Well, there is a solution to your filter misery. Let me take you through this interesting DAX function called CROSSFILTER.

CROSSFILTER is a function that lets you control the filter direction or remove filter context between two tables in a relationship. It can help you overcome some of the most common and frustrating scenarios that you encounter in DAX such as filtering a dimension table by a measure from a fact table or filtering a fact table by a fact table where there’s no direct relationship between them.

The way Power BI setup relationships between tables are using one-to-many notation, which would mean the filter context flows from the dimension table(one) to the fact table(many). For example, if you have a DimCustomer and FactInternetSales table, and you create a relationship between them using the CustomerKey field, then any filter applied to the DimCustomer table will filter the FactInternetSales table as well. It’s reasonable to want to see only the sales related to the selected customers.

However, sometimes you may want to do the opposite: filter the customer table based on the sales table. For instance, you may want to see only the number of customers who has a purchase by region. This is not possible with the default relationship direction, because the region is stored under the DimSalesTerritory table and customers are stored under the DimCustomer table. Both of these dimension tables filter a FactInternetSales table with a one-to-many relationship.

Let’s work this together in our favorite AdventureWorks database. Create a measure to calculate the number of customers and drop that onto a table visual with region and sales amount.

# Customers = COUNTROWS(DimCustomer)

You can see from the visual that it lists all regions whether there is a sale or not and displays the same number for our # Customers measure. Let us fix that problem using CROSSFILTER in a CALCULATE function. You can change the direction of the relationship to be bidirectional (both). This way, you can filter one dimension table based on another table regardless of the relationship direction.

Here’s how you can create a measure to fix that issue:

# Customers with CROSSFILTER =
CALCULATE (
    [# Customers],
    CROSSFILTER ( 
      FactInternetSales[CustomerKey], 
      DimCustomer[CustomerKey], 
      BOTH 
    )
)

Congratulations!! You’ve fixed the problem.

The function takes three arguments. The first two are the columns that have an existing relationship between those tables and the last one would be the filter direction, which can be one of the following: Both, None, and One. To use this function effectively, always wrap it inside CALCULATE function, which allows you to change the filter context for a given expression.

Now you know, CROSSFILTER is a powerful function that can help you customize your DAX calculations based on different filter scenarios and improve your model performance by reducing unnecessary filtering operations. However, it’s important to use it with caution and test your results carefully because it can also introduce unexpected behaviors or errors if used incorrectly.

This article was published in Microsoft Power BI publication, don’t forget to sign it for more interesting Power BI Tutorials, Tips and Tricks.

If you’re looking to level up your DAX skills, check out my blog post on other relationship functions! USERELATIONSHIP and RELATED & RELATEDTABLE

If you enjoyed this blog post and learned something useful and would like to receive notifications about my new blogs, please follow and subscribe to my Medium account. Also, please don’t forget to give me a clap and save it on your reading list. Stay tuned for my next blog posts with tons of other Power BI tips and tricks. Feel free to leave your comments and feedback below. Thank you for visiting!

Keep on learning!