Power Pivot Principles: The A to Z of DAX Functions – ALLCROSSFILTERED

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at ALLCROSSFILTERED.

The ALLCROSSFILTERED function

The ALLCROSSFILTERED function clears all filters which are applied to a table.  It has the following syntax:

The argument is defined as follows:

• table: this is required and represents the table you wish to clear the filters on.

It should be noted that:

• ALLCROSSFILTERED may only be used to clear filters but not return a table
• ALLCROSSFILTERED can be used only as a CALCULATE modifier and cannot be used as a table function
• ALLCROSSFILTERED removes all the filters on an expanded table (like ALL) and on columns and tables that are cross-filtering the table argument because of bidirectional cross-filters set on relationships directly or indirectly connected to the expanded table
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Please see my example below:

Consider the Table Sales:

and another Table Categories:

They are linked as follows:

Do ensure (by double-clicking on the link) that the ‘Cross filter direction’ is set to ‘Both’, viz.

I now create three [3] measures as follows:

1. Total Sales = SUM(Sales[Amount])
2. ALL Categories Sales = CALCULATE([Total Sales], ALL(Categories))
3. ALLCROSSFILTERED Categories Sales = CALCULATE([Total Sales], ALLCROSSFILTERED(Categories))

This results in the following output matrix in Power BI (ALLCROSSFILTERED only works in Power BI, not Power Pivot in Excel presently):

In more complicated models, ALLCROSSFILTERED will have further propagated effects. Consider the following arrangement:

Here, using ALLCROSSFILTERED on the Product table will remove the (cross) filters to both the Sales and Imagery tables too.

Come back next week 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.