Export (0) Print
Expand All

ISFILTERED Function (DAX)

Returns TRUE when columnName is being filtered directly. If there is no filter on the column or if the filtering happens because a different column in the same table or in a related table is being filtered then the function returns FALSE.

ISFILTERED(<columnName>)

columnName

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

TRUE when columnName is being filtered directly.

  • columnName is said to be filtered directly when the filter or filters apply over the column; 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 the column by filtering it as well.

  • The related function ISCROSSFILTERED Function (DAX) returns TRUE when columnName or another column in the same or related table is being filtered.

The following example creates a measure and then presents different scenarios to explain the behavior of ISFILTERED(). The scenarios can be followed by downloading the Data Analysis Expressions (DAX) Sample Data spreadsheet.

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

=ISFILTERED(Product[ProductName])

Understanding ISFILTERED():

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

    Is ProductName Filtered directly

    FALSE

    If nothing appears in the pivot table, drag [Is ProductName Filtered directly] to the Values box.

  2. Drag ProductCategory[ProductCategoryName] to the Row Labels box; all values should still be FALSE and your table should look something like this:

    Row Labels

    Is ProductName Filtered directly

    Accessories

    FALSE

    Bikes

    FALSE

    Clothing

    FALSE

    Components

    FALSE

    FALSE

    Grand Total

    FALSE

    The reason the table contains values of FALSE is because ProductCategory[ProductCategoryName] filters indirectly or cross-filters Product[ProductName], but is not a direct filter on the column.

  3. Drag Product[Status] to the Column Labels box; all values should still be FALSE and your table should look something like this:

    Is ProductName Filtered directly

    Column Labels

    Row Labels

    Current

    Grand Total

    Accessories

    FALSE

    FALSE

    FALSE

    Bikes

    FALSE

    FALSE

    FALSE

    Clothing

    FALSE

    FALSE

    FALSE

    Components

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    FALSE

    Grand Total

    FALSE

    FALSE

    FALSE

    The table still has many values of FALSE because Product[Status] doesn't filter Product[Name] directly.

  4. The last step in this process is to drag Product[Name] to the Report Filter box. At this point nothing should have changed; however, once you select a product name, the table values should change to TRUE. Depending on the product you selected your table should look like this:

    ProductName

    Mountain-400-W Silver, 38

    Is ProductName Filtered directly

    Column Labels

    Row Labels

    Current

    Grand Total

    Accessories

    TRUE

    TRUE

    Bikes

    TRUE

    TRUE

    Clothing

    TRUE

    TRUE

    Components

    TRUE

    TRUE

    TRUE

    TRUE

    Grand Total

    TRUE

    TRUE

  5. In conclusion, ISFILTERED() allows you to determine if columnName is being filtered directly in the context where your expression is being evaluated.

This example uses the DAX_AdventureWorks sample spreadsheet model

Community Additions

ADD
Show:
© 2014 Microsoft