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 :

https://technet.microsoft.com/en-us/library/ee634802.aspx

https://technet.microsoft.com/en-us/library/ee634795.aspx