Power Pivot Principles: ALLEXCEPT

Welcome back to our Power Pivot blog. Today we discuss how to use the ALLEXCEPT function in DAX.

In our previous blog we talked about using the ALL function together with the CALCULATE function to sum values up that will not be affected by a filter. Here, we turn the idea on its head with the ALLEXCEPT function.

As a refresher, the ALL function used in conjunction with the CALCULATE function:

This will result in a column that always sums the total sales of the product category, no matter what filter is applied to this field.  If we wish to subject other fields: e.g. ProductKey and CustomerKey, to the ALL function we can use a function like this:

It’s pretty simple, but the formula may get unnecessarily long quite quickly.  This function will ensure that the order date, the product key and the customer key will not be knocked affected by filters on these fields.

But what if we have a table with 30 fields and we don’t want 29 of the fields to be knocked off instead of just three?  We would have to write up a formula with 29 ALL functions!

This is where we can use the ALLEXCEPT function.  Consider our Sales table once more:

Let’s say we don’t want to filter any of the fields except for OrderDate. We can use the following CALCULATE and ALLEXCEPT combination:

This measure will then allow us to filter all of the other fields, except for the Order Date field in the Sales table.  Do note the difference in syntax, where the ALLEXCEPT function requires us to specify the table first before the field:

If you think about it, this makes sense.  If we are saying we want to only except certain fields we need to specify the set of fields we are talking about  - that is, the table.

That’s it for this week, stay tuned to our blog for more on Power Pivot.  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.