Downloads
DAXInformationAdvanced

ISCROSSFILTERED

Returns TRUE when a column is filtered indirectly, through a relationship with another table.

Syntax
ISCROSSFILTERED(<columnName>)

Returns: TRUE or FALSE (a scalar).

What it does

ISCROSSFILTERED tells you whether a column is being filtered by something other than its own filter. The usual cause is a relationship. A slicer on the sales table filters the dates that have sales, and that filter reaches the date table through the relationship. ISFILTERED would miss this; ISCROSSFILTERED catches it. It is the test for "this column is narrowed by the data, not by a slicer on it."

Syntax and arguments

Example

The pack shows a list of years on the page. When the data is filtered by a product slicer, the year list should only show years with matching sales. The note tells the reader why some years are missing.

Year Note =
IF(
    ISCROSSFILTERED('Date'[Year]),
    "Years shown only where the selected data has activity",
    "All years shown"
)

If the sales data has been filtered, the year column is cross filtered, and the note explains the list. With no filter in the model, it reads "All years shown".

Example: a second, different use

Check whether a store list is being narrowed by a product choice, so a store count is read correctly:

Stores Narrowed = ISCROSSFILTERED(Stores[StoreName])

A TRUE result means the stores visible are those that sold the chosen products. A count of stores then means stores with activity, not all stores in the company.

Things to watch

In business terms

ISCROSSFILTERED is used to explain a list that looks shorter than it should: a customer list that only shows accounts with activity in the period, or a vendor list narrowed by a cost center. It keeps the reader from mistaking "no activity" for "not in the company."

Related functions

All DAX functions · View as Markdown · DAX fundamentals