The CROSSFILTER function specifies the direction of the filter to use when evaluating a DAX expression that involves data from two related tables.
CROSSFILTER(
columnName1,
columnName2,
direction
)
- columnName1: Name of the column that determines the relationship in the first table.
- columnName2: Name of the column that determines the relationship in the second table.
- direction: Direction of the filter to apply.
The CROSSFILTER function does not return a value.
The value of the direction argument can be:
- one or 1: The table filter corresponding to the "one" side of the relation (in one-to-many relationships) filters the table corresponding to the "many" side.
- both or 2: Both filters are applied on the relationship.
- none or 0: No filters are applied on the relationship.
In the case of a "one-to-one" relationship, there are no differences between types one and both.
CROSSFILTER can only be used in functions that accept a filter as an argument. For example:
- CALCULATE
- CALCULATABLE
- OPENINGBALANCEMONTH
- OPENINGBALANCEQUARTER
- OPENINGBALANCEYEAR
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- TOTALMTD
- TOTALQTD
- TOTALYTD
The function uses relationships that have already been defined. If any of the columns included as arguments are not part of a relationship, the function returns an error.
The CROSSFILTER function sets a direction for the relationship regardless of the settings applied to the relationship in the model view.
In the case that CALCULATE functions are nested and more than one contains a CROSSFILTER function, the innermost one will be imposed in case of conflict or ambiguity.
In the following model:
...it is possible to calculate the number of sales per year, since the filter between the Calendar and Sales table is directed this way (from Calendar to Sales in a one-to-many relationship):
We could also calculate the number of sales per product because, again, between the Products and Sales table there is a one-to-many relationship in which the filter flows in this direction:
However, it would not be possible to calculate the number of products sold per year (or in some other unit of time):
In the previous image we see how the Date field of the calendar has been brought into a matrix together with the count of the Product Id field, which we would expect to return the number of different products sold per unit of time. However, the calculation returns the value 198 in all cases (a figure equal to the total of products sold in the entire period).
To solve this we can do two things: 1) Modify the relationship between the Sales and Products tables so that the filter flows in both directions, or 2) count the Product Id field using a CALCULATE with the CROSSFILTER function added as argument specifying that the filtering should be in both directions. Let's look at this last option. We define the following measure:
Number of Products =
CALCULATE(
DISTINCTCOUNT(Products[Product Id]),
CROSSFILTER(Products[Product Id], Sales[Product Id], both)
)
As arguments of the CROSSFILTER function we indicate the fields that limit the relationship in both tables and the desired direction in the relationship ("both"). We take this measure to the previous matrix:
Now the calculation is done correctly.
