Power Pivot Principles: ISCROSSFILTERED vs ISFILTERED Functions

Welcome back to the Power Pivot Principles blog.  This week, we will talk about the difference between the ISCROSSFILTERED and the ISFILTERED functions in DAX.

A while ago, we wrote about the ISFILTERED function.  Now, we think it’s also worth mentioning the ISCROSSFILTERED function.

To recap, the ISFILTERED function has the following syntax:

This returns a TRUE value when the columnName parameter is filtered in the PivotTable (by definition, all simple, non-total rows and columns in a PivotTable are filtered by a given context).  If there is no filter applied to the columnName, the function will return with FALSE.

Similarly, the ISCROSSFILTERED returns TRUE when the column has a filter because of automatic propagation of another filter and not because of a filter applied directly to it.  This function has the following syntax:

The difference between the two functions is that 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, where the ISFILTERED function will return TRUE.

To prevent confusing you, let’s demonstrate with an example.  Consider that we have Sales data by stores and different products already loaded to the Power Pivot Data Model: 

We also have another table of ‘Store Key’ and Branch data loaded into the Data Model:

In the Diagram view, we define the relationship between the two tables, by connecting the ‘Store Key’ fields in the two tables:

Next, we will create two measures:

• ‘ISFILTERED Store’:

• ‘ISCROSSFILTERED Store’:

Going back to Excel, we will create a PivotTable and a Branch slicer:

Initially, the ‘ISFILTERED Store’ returns all values as TRUE because it has the direct filter on it, which basically is the ‘Store Key’ that we put on the table. It is going row by row, giving us the unique value of these. Notice that the ‘Grand Total’ value of ‘ISFILTERED Store’ column is FALSE. Meanwhile, we haven’t selected anything on the Branch slicer, so all values return FALSE.

Now, we select a value on the slicer:

The ‘ISCROSSFILTERED Store’ column returns all TRUE values, including the ‘Grand Total’. This happens based on the relationship between ‘Store Key’ of the two tables, in this case, the ‘Store Key’ is being filtered by its related field in a different table, which is regarded as the indirect filter.

Clear as mud?

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about >here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.