ISCROSSFILTERED
Returns TRUE when a column is filtered indirectly, through a relationship with another table.
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
- columnName: a column in a table that is related to the table being filtered, such as
'Date'[Year].
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
- ISCROSSFILTERED depends on the direction of the relationship. A filter that does not travel toward the column does not count.
- A filter applied directly to the column is ISFILTERED, not cross filtering. Test both when you need the full picture.
- The test is about the model structure, so changing a relationship changes the answer.
- Bidirectional relationships can cross filter more than you expect. Review them before relying on the result.
- It is an advanced tool. Start with ISFILTERED and add this only when a note or rule needs the indirect case.
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."