ISFILTERED

How to Use the Power BI DAX function ISFILTERED

In this article, we’ll be taking a closer look at the ISFILTERED function in DAX, which is one of the most useful functions in the language. We’ll explore what the function does, how it works, and provide some practical examples of how you can use it in your data analysis processes.

What is the ISFILTERED function?

The ISFILTERED function in DAX is used to determine whether a column or table is filtered or not. In other words, it returns a Boolean value (TRUE or FALSE) based on whether a filter context exists for the specified column or table. The filter context is the set of values that have been selected for the column or table in question, and it can be filtered by slicers, filters, or other visuals in the report.

How does the ISFILTERED function work?

The syntax for the ISFILTERED function is as follows:

ISFILTERED(❰ColumnName❱ | ❰TableName❱)

You can use either a column name or a table name as the input parameter for the function. If you use a column name, the function will return TRUE if the column is part of the filter context. If you use a table name, the function will return TRUE if any column in the table is part of the filter context.

Here's an example to illustrate how the function works. Let's say you have a table called Sales with two columns: Region and SalesAmount. You also have a slicer in your report that filters the Region column based on user selection. You can use the following DAX formula to determine whether the Region column is filtered or not:

ISFILTERED(Sales[Region])

If the slicer has a value selected for the Region column, the function will return TRUE. If no value is selected, the function will return FALSE.

You can also use the function with table names, like this:

ISFILTERED(Sales)

This formula will return TRUE if the Region column is filtered, or if any other column in the Sales table is filtered.

Practical examples of using ISFILTERED

Now that you have a basic understanding of how the ISFILTERED function works, let's explore some practical examples of how you can use it in your data analysis processes.

Example 1: Displaying a message based on filter selection

Suppose you have a report that shows sales data for different regions and products. You want to display a message to the user based on the filter selection they make. For example, if the user selects a single region, you want to show a message that says "Sales data for Region X". If they select multiple regions, you want to show a message that says "Sales data for Multiple Regions".

You can use the ISFILTERED function to achieve this. Here's the formula you can use in a text box visual:

IF(ISFILTERED(Sales[Region]), “Sales data for ” & IF(COUNTROWS(VALUES(Sales[Region]))❱1,”Multiple Regions”,VALUES(Sales[Region])), “Sales data for All Regions”)

This formula checks whether the Region column is filtered. If it is, it checks how many values are selected using the COUNTROWS and VALUES functions. If more than one value is selected, it shows the "Multiple Regions" message. Otherwise, it shows the selected region.

Example 2: Calculating the percentage of filtered data

Suppose you have a table that shows sales data for different products and months. You want to calculate the percentage of sales that belong to the current filter context. In other words, you want to know what percentage of the total sales are represented by the selected products and months.

You can use the ISFILTERED function to achieve this. Here's the formula you can use:

IF(ISFILTERED(Sales[Product]) || ISFILTERED(Sales[Month]), DIVIDE(SUM(Sales[SalesAmount]), CALCULATE(SUM(Sales[SalesAmount]),ALL(Sales))),SUM(Sales[SalesAmount]))

This formula checks whether either the Product or Month columns are filtered. If they are, it divides the sum of the SalesAmount column by the sum of SalesAmount for all products and months. This gives you the percentage of filtered data. If none of the columns are filtered, it simply shows the sum of SalesAmount.

The ISFILTERED function in DAX is a powerful tool that enables you to determine whether a column or table is filtered or not. By using this function, you can create more sophisticated calculations and expressions that take into account the filter context of your data. The practical examples we've explored in this article are just a small sample of the ways you can use this function to enhance your data analysis capabilities in Power BI.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)