How does the CROSSFILTER function (DAX) work?

The CROSSFILTER function (DAX) defines the cross-filtering direction that can be used in a calculation between two columns.

CROSSFILTER Formula Syntax

     <columnName1>, <columnName2>, <direction>

How do you use the CROSSFILTER function?

Use CROSSFILTER to change the join type for relationships. With this function, you can change how the cross-filter direction behaves between two columns defined by a relationship. It can be used in functions that have a filter for an argument.

Related Blog Posts

Related Support Forum Posts

Handling Bi-directional Relationship
One Dimension, Multiple Facts -and- Multiple Interconnected Facts
Cross Filter Both Direction

Considerations when using the CROSSFILTER function

In CROSSFILTER, the cross-filtering setting of a relationship is not important. This function will override any cross-filtering setting.

An error occurs if any of the columns named as an argument is not part of a relationship, or if the arguments belong to different relationships.

If expressions with the CALCULATE function are nested and more than one expression uses CROSSFILTER, then the innermost CROSSFILTER is the one that prevails.

Related Video Tutorials

Formula examples using the CROSSFILTER function

= CALCULATE([Distinct Count of ProductKey], CROSSFILTER(FactInternetSales[ProductKey], DimProduct[ProductKey] , Both))** “`

= CALCULATE (Sales[Distinct Count of Products], CROSSFILTER (Sales[Product],Products[Product],Both))

= crossfilter([{a: 1, b: 2}, {b: 1, c: 3}, {a: 0, b: 2, c: 4}]);

Related Course Modules




Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.

Download DAX Formulas Reference Guide
Download DAX Formula Reference Guide