CROSSFILTER Function (DAX)

How does the CROSSFILTER function (DAX) work?

The CROSSFILTER function (DAX) specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns.

CROSSFILTER Formula Syntax

CROSSFILTER(
     <columnName1>, <columnName2>, <direction>
)

How do you use the CROSSFILTER function?

The CROSSFILTER can only be used in functions that take a filter as an argument, for example: CALCULATE, CALCULATETABLE, CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR, OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR, TOTALMTD, TOTALQTD and TOTALYTD functions.

Used CROSSFILTER function to change how the cross-filter direction behaves between two columns defined by a relationship

Related Blog Posts

Related Support Forum Posts

One Dimension, Multiple Facts -and- Multiple Interconnected Facts
Getting the correct subtotal DAX
Accounts activated but with no sales registered

Considerations when using the CROSSFILTER function

  • In CROSSFILTER, the cross-filtering setting of a relationship is not important; that is, whether the relationship is set to filter one, or both directions in the model does not affect the usage of the function. CROSSFILTER will override any existing cross-filtering setting.
  • An error is returned if any of the columns named as an argument is not part of a relationship or the arguments belong to different relationships.
  • If CALCULATE expressions are nested, and more than one CALCULATE expression contains a CROSSFILTER function, then the innermost CROSSFILTER is the one that prevails in case of a conflict or ambiguity.

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

Ultimate-Beginners-Guide-To-PowerBI
Center of Excellence

DOWNLOAD


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
Membership