ISCROSSFILTERED

How to Use the Power BI DAX function ISCROSSFILTERED

In this article, we will discuss in detail what the ISCROSSFILTERED function is, how it works, and how it can be used to enhance your data analysis in Power BI.

What is the ISCROSSFILTERED Function?

The ISCROSSFILTERED function is a DAX function that is used to determine whether a specified column has been filtered directly or indirectly. This function returns a Boolean value of TRUE or FALSE, depending on whether the specified column is being filtered or not.

The syntax for the ISCROSSFILTERED function is as follows:


ISCROSSFILTERED(❰ColumnName❱)


Where ❰ColumnName❱ is the name of the column that you want to check if it is being filtered.

How Does the ISCROSSFILTERED Function Work?

The ISCROSSFILTERED function works by checking if the specified column has been filtered directly or indirectly. If the specified column is being filtered directly, then the function returns TRUE. If the specified column is not being filtered directly, but is being filtered indirectly through another column, then the function returns FALSE.

For example, let's say we have a table called "Sales" with two columns, "Region" and "Sales Amount". If we apply a filter on the "Region" column, then the ISCROSSFILTERED function returns TRUE for the "Region" column, as it is being filtered directly. However, if we apply a filter on the "Sales Amount" column, then the ISCROSSFILTERED function returns FALSE for the "Region" column, as it is being filtered through another column.

How to Use the ISCROSSFILTERED Function in Power BI

The ISCROSSFILTERED function can be used in combination with other DAX functions such as CALCULATE and FILTER to achieve more complex calculations and analysis. Here are some examples of how the ISCROSSFILTERED function can be used in Power BI:

Example 1: Calculating Sales for Directly Filtered Regions

Let's say we want to calculate the total sales amount for the regions that are directly filtered. We can use the following DAX expression:


Total Sales (Directly Filtered) =

CALCULATE(

SUM(‘Sales'[Sales Amount]),

ISCROSSFILTERED(‘Sales'[Region])

)


This DAX expression uses the CALCULATE function to calculate the total sales amount for the "Sales" table. The ISCROSSFILTERED function is used to filter the "Region" column, and only the regions that are directly filtered will be included in the calculation.

Example 2: Calculating Sales for Indirectly Filtered Regions

Let's say we want to calculate the total sales amount for the regions that are indirectly filtered through another column. We can use the following DAX expression:


Total Sales (Indirectly Filtered) =

CALCULATE(

SUM(‘Sales'[Sales Amount]),

NOT(ISCROSSFILTERED(‘Sales'[Region]))

)


This DAX expression uses the CALCULATE function to calculate the total sales amount for the "Sales" table. The NOT function is used to filter out the regions that are directly filtered, and only the regions that are indirectly filtered through another column will be included in the calculation.

In this article, we have discussed what the ISCROSSFILTERED function is, how it works, and how it can be used to enhance your data analysis in Power BI. The ISCROSSFILTERED function is a powerful tool that allows you to determine whether a specified column is being filtered directly or indirectly, and can be used in combination with other DAX functions to achieve more complex calculations and analysis.

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

Upcoming Courses

11-13 November 2024

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)