CROSSFILTER
Changes how filters flow through an existing relationship, for one calculation.
CROSSFILTER(<columnName1>, <columnName2>, <direction>)Returns: Used inside CALCULATE. It is not a value by itself.
What it does
CROSSFILTER sets the filter direction of an existing relationship inside one calculation, without changing the model. Normally a relationship passes filters from the one side to the many side. CROSSFILTER can make the filter pass both ways (BOTH), or stop it for this calculation (NONE). Use it when one measure needs a different view from the rest of the report.
Syntax and arguments
- columnName1 and columnName2: the two columns of the existing relationship, such as
Sales[StoreKey]andStores[StoreKey]. - direction:
NONEturns the relationship off for this calculation.ONEWAYis the normal direction, from the one side to the many side.BOTHpasses filters in both directions.
Example
Total sales for the whole company, while the other filters on the page still apply. The store slicer must not reach the Sales table for this measure.
Sales All Stores =
CALCULATE (
[Total Sales],
CROSSFILTER ( Sales[StoreKey], Stores[StoreKey], NONE )
)
With NONE, filters on Stores no longer reach Sales inside this calculation. The date slicer and other filters still apply, so the figure is the company total for the period on screen.
Example: a second use
Count the products that had sales in the current filter. The filter must travel from Sales back to Product.
Products With Sales =
CALCULATE (
COUNTROWS ( Product ),
CROSSFILTER ( Sales[ProductKey], Product[ProductKey], BOTH )
)
With BOTH, the sales in the current filter reduce the Product rows to those with activity. The count is the number of products sold in the period.
Things to watch
- The change applies only to the calculation. The model is not altered.
BOTHcan slow other visuals and can create ambiguous filter paths in complex models. Keep it inside a single measure.NONEremoves the relationship's effect for this measure. Name the measure so readers know it ignores that dimension.- CROSSFILTER does not create a relationship. The relationship must exist.
USERELATIONSHIPactivates an inactive relationship. CROSSFILTER changes the direction or turns off an existing one. The two are often used together.
In business terms
Measures that must ignore one dimension, such as company sales next to a store slicer, and counts of dimension members with activity, such as customers or products sold in the period. Pick the direction on purpose and label the measure so the figure is not misread.