Power BI Isfiltered vs Iscrossfiltered in Dax
Power BI Isfiltered and Iscrossfiltered functions are sometimes very confusing for the newbie. These DAX functions are beneficial when we develop very advanced dashboards in Power BI, where we need to check multiple filtering conditions on the columns that are being filtered. We can’t control the user’s behavior and how they will interact with the dashboards and which slicer they will choose to see results. Isfiltered and Iscrossfiltered functions in DAX help us to figure out that which options the user has selected. Once we can capture the user’s activity on the dashboard, we are good to go, and we can show what we want to reveal to the user.
Effect of filters on Power BI Isfitlered and Iscrossfiltered functions
There are three types of filters that we need to consider while working wit Isfiltered & Iscrossfiltered in DAX.
- Direct Filter
- Indirect Filter
- Cross Filter
Direct filters are those filters that effect directly on the column that is being used. For example, if we apply the filter on the “Occupation” column in the Customer table, it will be called direct filter because it filters “Occupations” directly, as you can see in the above Customer table figure. If I choose “Skilled Manual.” from the occupation column, I will get all those rows where occupation is “Skilled Manual.”.
The indirect filter is that filter, which is not the direct filter. For example, if we choose any value from the “Gender” column, it will indirectly filter “Occupations.” So the “Gender” filter will be called an indirect filter.
For elaborating the cross filter, we need at least two tables so that we can get one to many relationships. For this, we have Territory and Customer table. The Territory table is on the one side, and the Customer table is on the many side. If we select any value from any column within the Territory table, then the filter that propagates to the Customer table will be called a cross filter.
Power BI Isfiltered function with direct filter
We need to write some measures to explain the working mechanism of the Power BI Isfiltered function.
IsF(Occ) = ISFILTERED(Customers[Occupation])
IsF(Country) = ISFILTERED(Territory[Country])
If we drag the “Occupation” column to the visual in Power BI and the newly created measures, you will see the following result.
IsF(occ) is returning True values while IsF(Country) measure is returning False values.
Why is it happening?
IsFiltered function only works with direct filters. It returns true when it is applied to the direct filter. IsF(Occ) measure is returning true value because we have “Occupation” values on rows producing direct filters.
IsF(country) measure is showing false values because due to “Occupation” on rows, it is acting as an indirect filter for “Country.”
It would help if you remembered that the Isfiltered function returns true values only for direct filters.
Isfiltered function with Cross Filter
Let’s add one slicer called “Country,” based on the country column from the Territory table and create one more measure named
IsF(Marital) = ISFILTERED(Customers[MaritalStatus])
Drag that newly created measure on the visual. If you choose any value from the country slicer, IsF(country) measure will return True values as you can see below.
We have applied the IsFiltered function on the “Country” column. It returned false values because, on the visual, the filter was coming from the occupation column as an indirect filter. But due to the country slicer, now it is returning true because a direct filter is coming from the slicer.
IsF(Marital) shows false values if we select any value from the slicer as the cross filter.
IsFiltered function with Indirect Filter
Let’s add the “Gender” column on the visual and see how the IsFiltered function behaves. You can see below both IsF(Occ), and IsF(Country) measures are returning false because the Isfiltered function doesn’t take indirect filter into account.
Now try to choose any value from the country slicer; you will see IsF(Country) measure will return true values because it is getting a direct filter from the slicer.
Add one more slicer on the report. Assign the “Occupation” column from the customer table to the slicer. If you select any occupation from the slicer, IsF(Occ) measure will return true because now it is getting a direct filter.
Iscrossfiltered function in DAX
Iscrossfiltered returns true with direct, indirect, and cross filter. For elaborating Iscrossfiltered function in dax, we need to create two measures.
IsCF(Occu) = ISCROSSFILTERED(Customers[Occupation])
IsCF(Country) = ISCROSSFILTERED(Territory[Country])
If you have a look at the following figure, IsCF(Occu) returns true becuase of direct filter coming from Occupation column. IsCF(Country) measure is displaying false values becuase there is no country filter there at all.
If we select any value from the country slicer, IsCF(country) will return true because of the country table’s direct filter.
Iscrossfiltered function with indirect & cross filter
Let’s create one more measure named IsCF(Marital) = ISCROSSFILTERED(Customers[MaritalStatus]) and add gender column on the visual. Filter from the gender column will be an indirect filter for IsCF(Marital) and returns True values.
If you create another slicer based on Group column from the Territory table and choose any value, IsCF(Country) will return true values because the filter from the “Group” column propagates as the cross filter.
DAX measures with Isfiltered & Iscrossfiltered functions
Let’s create some measures to see how we can use isfiltered and iscrossfiltered functions practically. For this, we need to create the following measures.
Total Sales = SUMX(Sales,Sales[SalesAmount] + Sales[Freight])
Sales IsF = IF(ISFILTERED(Territory[Country]),[Total Sales],BLANK())
Sales IsCF = IF(ISCROSSFILTERED(Territory[Country]),[Total Sales],BLANK())
If we select any value from the country slicer, then “Sales IsF” will return total sales, and it will return blank values for other slicers.
If we select any value from the country slicer, then the “Sales IsCF” measure will display total sales because It will be a direct filter coming from the country slicer.
If we select any value from the “Group” slicer, then again “Sales IsCF” measure will return total sales because of the indirect filter from another column but the same table.
If we create an occupation slicer from the Customer table and choose any value, then “Sales IsCF” will not return any value because the filter cannot propagate from many side to one side.
Let’s try to change the cross filter direct from single to both between the Customer and Territory tables. This time “Sales IsCF” will return total sale because now filters are propagating from many side to one side.
Read more about IF function in DAX
For more useful blogs, please visit Learn DAX
