The DAX functions, ALL, ALLSELECTED, and ALLEXCEPT can be thought of as sisters, as while they may look and behave somewhat similarly they are all unique in their own way. ALLEXCEPT was the first of these sisters (functions) I came across. Let's look at what makes her stand out from the rest.
Just like its sisters ALLEXCEPT can be thought of as a 'filtering' function. ALLEXCEPT allows you to remove filters from ALL columns in a table EXCEPT for those specified within the function.
ALLEXCEPT's main use case is when you are trying to create calculations within a particular context while wanting to ignore certain filters applied to those columns.
For example, lets imagine we wanted to look at the total sales for each of our product categories across each year. If we just added these three fields into our table, as expected, we would get a distinct total for each category and year. As seen below:
But what if I then wanted to see how each of these values compared to the total sales for each year regardless of category. To do this I would want to remove all other 'filters' (e.g., product category) that are affecting my value apart from 'Year'. We can do that using this calculation:
Our table now returns back the total sales for each year, ignoring our category field.
Similarly we could do the same for if we wanted to compare our original values to the total sales for each category, regardless of year. We could create this measure:
Which when brought into our table would return these values:
Hopefully within these examples we can see how and why ALLEXCEPT could become very useful within Power BI. To finalise, let us see how these measures we've made could be used.
I've now turned our original total sales table into a line chart with large markers for each year. Additionally I've made a KPI which will allow us to compare our total sales for each category & year to out total sales for each year. Below, my KPI is highlighted on the total sales for furniture products in 2022, with the target below related to the total sales for each year.
If I was to choose another year within our 'furniture column' our total sales value would change, but our yearly total sales would remain the same.