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

DOWNLOAD

To access our comprehensive DAX formula reference guide just insert your email address below. The resource will download immediately and we’ll also email it to you.