CROSSFILTER

CROSSFILTER is a DAX function that specifies the cross-filtering direction to be used in the evaluation of a DAX expression, defining the relationship by naming the two columns that serve as endpoints.

Syntax

CROSSFILTER(
   LeftColumnName,
   RightColumnName,
   CrossFilterType
)

Argument	Properties	Description
LeftColumnName		Left Column.
RightColumnName		Right Column.
CrossFilterType		The third argument to the CROSSFILTER function should be 0 for None or 1 for OneWay, or 2 for Both. It is also possible to use words None, OneWay, Both.

Return Values

The function returns no value; the function only sets the cross-filtering direction for the indicated relationship, for the duration of the query.

Remarks

• In the case of a 1:1 relationship, there is no difference between the one and both direction.

• 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.

• CROSSFILTER uses 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 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.

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

In the following model diagram, both DimProduct and DimDate have a single direction relationship with FactInternetSales.

By default, we cannot get the Count of Products sold by year:

There are two ways to get the count of products by year:

• Turn on bi-directional cross-filtering on the relationship. This will change how filters work for all data between these two tables.

• Use the CROSSFILTER function to change how the relationships work for just this measure.

When using DAX, we can use the CROSSFILTER function to change how the cross-filter direction behaves between two columns defined by a relationship. In this case, the DAX expression looks like this:

BiDi:= CALCULATE([Distinct Count of ProductKey], CROSSFILTER(FactInternetSales[ProductKey], DimProduct[ProductKey] , Both))

By using the CROSSFILTER function in our measure expression, we get the expected results:

Related functions

Other functions related to CROSSFILTER are:

• RELATED
• RELATEDTABLE
• USERELATIONSHIP

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/crossfilter-function-dax