CROSSFILTER DAX Function in Power BI

The CROSSFILTER DAX function either modifies the relationship cross filter direction (to one or both), or it disables filter propagation (none). It’s useful when we need to change or ignore model relationships during the evaluation of a specific calculation.

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

DAX Syntax CROSSFILTER(LeftColumnName, RightColumnName, direction)

The function has the following parameters:

LeftColumnName: The name of an existing column, using standard DAX syntax and fully qualified, that usually represents the many side of the relationship to be used; if the arguments are given in reverse order the function will swap them before using them. This argument cannot be an expression.
RightColumnName: The name of an existing column, using standard DAX syntax and fully qualified, that usually represents the one side or lookup side of the relationship to be used; if the arguments are given in reverse order the function will swap them before using them. This argument cannot be an expression.
Direction: The cross-filter direction to be used. Must be one of the following:

None: No cross-filtering occurs along this relationship.
Both: Filters on either side filters the other side.
OneWay: Filters on the one side or the lookup side of a relationship filter the other side. This option cannot be used with a one-to-one relationship. Don’t use this option on a many-to-many relationship because it is unclear which side is the lookup side; use OneWay_LeftFiltersRight or OneWay_RightFiltersLeft instead.
OneWay_LeftFiltersRight: Filters on the side of LeftColumnName filter the side of RightColumnName. This option cannot be used with a one-to-one or many-to-one relationship.
OneWay_RightFiltersLeft: Filters on the side of RightColumnName filter the side of LeftColumnName. This option cannot be used with a one-to-one or many-to-one relationship.

Note: 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.

When to Use CROSSFILTER • When we need temporary bidirectional filtering for a specific calculation.
• When creating measures that should ignore existing relationships.
• When implementing complex filtering logic that requires different filter directions for different scenarios

Example: Let’s have the following data model in our Power BI report.

The data model contains three tables. For subsequent calculations, we will focus on only two: the Students Table and the “Branch Table”. These tables share a one-to-many relationship, where the Branch ID column in the “Students Table” references the “ID No” column in the “Branch Table”.

Let’s create a measure with name “Sum Measure”, which calculates the sum of 10th Marks column of the ‘Students Table’.

DAX

Sum Measure = SUM('Students Table'[10th Marks])

Let’s create a measure with name “Sum Measure1”, which calculates the sum of ID No column of the ‘Branch Table’.

DAX

Sum Measure1 = SUM('Branch Table'[ID No])

Let’s create a measure with name “Crossfilter Measure”, which calculates the sum of “ID No” column of the ‘Branch Table’. Also, we are using the CROSSFILTER function to modify the relationship to bidirectional filter the tables.

DAX

Crossfilter Measure =
CALCULATE (
    SUM ( 'Branch Table'[ID No] ),
    CROSSFILTER ( 'Students Table'[Branch ID], 'Branch Table'[ID No], BOTH )
)

The output of the above dax code is shown below:

Let’s select the value from the “Students Table”.

The Sum Measure is filtered, but the Sum Measure1 is not filtered, and Crossfilter Measure is filtered.

Previous Next