Whats up with ALL(Table) + ALLExcept + ALL(Table[Column])
I was Quizzed with Ranks in DAX and during my exploration few intresting things were found. Here are my findings on how ALL and ALLExcept function behaves with contexts !
- ALL(Table) will not apply for any of the contexts (Row,Query,Filter etc.)
- ALLExcept (Table,Table[Column]) will apply the context only when Table[Column] is specified in any context (Row,Query,Filter etc)
- ALL(Table[Column]) will not apply context filters for the specified columns i.e Table[Column] in any context (Row,Query,Filter etc)
Here is the structure of the "Doctors" Table : As you can observer the Spec and Hospital are from the same table from which i have created the measures which makes it intresting !
The measures were sliced against Hospital , Spec column to observe the behaviour :
ALLSpec :=CALCULATE(countrows(),ALL(Doctors[Spec]))
ALLEXceptSpec :=CALCULATE(COUNTROWS(),ALLEXCEPT(Doctors,Doctors[Spec]))
ALLTable :=CALCULATE(countrows(),ALL(Doctors))
Keep close attention to the the highlighted section
As you can notice from the section :
fig 1. Hospital filter context doesnt change ALLEXceptSpec measure as it will only change when the filter is on "Doctors[Spec]" Column .
fig 2. Spec filter context change ALLEXceptSpec measure as said above and ALLSpec measure ignores the spec filter .
Here are few more links on the same :