Delen via

ISCROSSFILTERED Function (DAX)

  • Artikel

Returns TRUE when columnName or another column in the same or related table is being filtered.

Syntax

ISCROSSFILTERED(<columnName>)

Parameters

  • columnName
    The name of an existing column, using standard DAX syntax. It cannot be an expression.

Return Value

TRUE when columnName or another column in the same or related table is being filtered. Otherwise returns FALSE.

Remarks

  • A column is said to be cross-filtered when a filter applied to another column in the same table or in a related table affects columnName by filtering it. A column is said to be filtered directly when the filter or filters apply over the column.

  • The related function ISFILTERED Function (DAX) returns TRUE when columnName is filtered directly.

Example

The following example creates a measure and then presents different scenarios to explain the behavior of ISCROSSFILTERED(). The scenarios can be followed by downloading the DAX_AdventureWorks spreadsheet.

First, create the following measure [Is ProductName Cross Filtered] in the [Product] table.

=ISCROSSFILTERED(Product[ProductName])

Understanding ISCROSSFILTERED():

  1. After you create the measure, the PivotTable should show that [Is ProductName Cross Filtered] is FALSE, because the expression is not being filtered at all. Now, you should have something like this:

    Is ProductName Cross Filtered

    FALSE

    If nothing appears in the PivotTable, drag the column [Is ProductName Cross Filtered] to the Values box.

  2. Drag DateTime[CalendarYear] to the Column Labels box and drag SalesTerritory[SalesTerritoryGroup] to the Row Labels box; all values should appear as FALSE. Now, you should have something like this:

    Is Product Name CrossFiltered

    Column Labels

    Row Labels

    2005

    2006

    2007

    2008

    Grand Total

    Europe

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    NA

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    North America

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    Pacific

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    Grand Total

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    You are seeing the value FALSE in all cells because neither DateTime[CalendarYear] or SalesTerritory[SalesTerritoryGroup] have any filtering effect over Product[ProductName].

  3. Drag the column ProductCategory[ProductCategoryName] to the Slicers Horizontal box and ProductSubCategory[ProductSubCategoryName] to the Slicers Vertical box. All values should still appear as FALSE, because when you add a column to the slicers box you haven't yet selected a slicing set. Therefore, you should have something like this:

    ProductCategoryName

    Accessories

    Bikes

    Clothing

    Components

    ProductSubcategoryName

    Is Product Name CrossFiltered

    Column Labels

    Bib-Shorts

    Row Labels

    2005

    2006

    2007

    2008

    Grand Total

    Bike-Racks

    Europe

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    Bike-Stands

    NA

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    Bottles and Cages

    North America

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    Bottom Brackets

    Pacific

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    Brakes

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    Caps

    Grand Total

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    Chains

    Cleaners

    Cranksets

    Derailleurs

    Fenders

    Forks

    Gloves

    Handlebars

    Headsets

  4. Select any item in the slicers and your table will now turn all cells to TRUE because you are now filtering the Product[ProductName] column through the related tables ProductCategory and ProductSubcategory. Your results should look like this:

    ProductCategoryName

    Accessories

    Bikes

    Clothing

    Components

    ProductSubcategoryName

    Is Product Name CrossFiltered

    Column Labels

    Bib-Shorts

    Row Labels

    2005

    2006

    2007

    2008

    Grand Total

    Bike-Racks

    Europe

    TRUE

    TRUE

    TRUE

    TRUE

    TRUE

    TRUE

    Bike-Stands

    NA

    TRUE

    TRUE

    TRUE

    TRUE

    TRUE

    TRUE

    Bottles and Cages

    North America

    TRUE

    TRUE

    TRUE

    TRUE

    TRUE

    TRUE

    Bottom Brackets

    Pacific

    TRUE

    TRUE

    TRUE

    TRUE

    TRUE

    TRUE

    Brakes

    TRUE

    TRUE

    TRUE

    TRUE

    TRUE

    TRUE

    Caps

    Grand Total

    TRUE

    TRUE

    TRUE

    TRUE

    TRUE

    TRUE

    Chains

    Cleaners

    Cranksets

    Derailleurs

    Fenders

    Forks

    Gloves

    Handlebars

    Headsets

  5. You can now replace the slicers with Product[ProductName] and Product[ModelName]. As long as you do not select any item on any slicer the measure returns FALSE; however, when you select an item in the slicer, the measure now returns TRUE because you are filtering Product[ProductName] by itself or by selecting a Product[ModelName].

  6. In conclusion, ISCROSSFILTERED() allows you to determine if columnName is being filtered in the context where your DAX expression is being evaluated.

This example uses the DAX_AdventureWorks sample spreadsheet model.

See Also

Reference

ISFILTERED Function (DAX)

FILTERS Function (DAX)

HASONEFILTER Function (DAX)

HASONEVALUE Function (DAX)