DAX Filter - CROSSFILTER function

Description

Specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns.

DAX CROSSFILTER function is new in Excel 2016.

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

Sr.No.	Parameter & Description
1	columnName1 The name of a column, fully qualified, that usually represents the many side or data (fact) table side of the relationship to be used. If the arguments are given in a reverse order, the function will swap them before using them. This argument cannot be an expression.
2	columnName2 The name of a column, fully qualified, that usually represents the one side or lookup table side of the relationship to be used. If the arguments are given in a reverse order, the function will swap them before using them. This argument cannot be an expression.
3	direction The cross-filter direction to be used − One − Filters on one or lookup table side of the relationship filter with many side. Both − Filters on either side filter the other. None − No cross-filtering occurs along this relationship.

Sr.No.

Parameter & Description

columnName1

The name of a column, fully qualified, that usually represents the many side or data (fact) table side of the relationship to be used.

If the arguments are given in a reverse order, the function will swap them before using them. This argument cannot be an expression.

columnName2

The name of a column, fully qualified, that usually represents the one side or lookup table side of the relationship to be used.

If the arguments are given in a reverse order, the function will swap them before using them. This argument cannot be an expression.

direction

The cross-filter direction to be used −

One − Filters on one or lookup table side of the relationship filter with many side.
Both − Filters on either side filter the other.
None − No cross-filtering occurs along this relationship.

DAX CROSSFILTER function does not return any value.

DAX CROSSFILTER function only sets the cross-filtering direction for the indicated relationship, for the duration of the query.

In the case of a 1:1 relationship, there is no difference between one and both direction.
CROSSFILTER can be used only in functions that takes a filter as an argument. For example, CALCULATE, CALCULATETABLE, CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR, OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR, TOTALMTD, TOTALQTD and TOTALYTD functions.
CROSSFILTER uses the existing relationships in the model, identifying relationships by their ending point columns.
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 crossfiltering 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.

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

dax_functions_filter.htm