Downloads
DAXRelationships and lookupAdvanced

CROSSFILTER

Changes how filters flow through an existing relationship, for one calculation.

Syntax
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

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals