Matthew Brice walks us through filters and calculations in DAX:
CALCULATE is somewhat unique in that it evaluates the 2nd, 3rd, …nth parameter first, and evaluates the first parameter last using values from my Filter Context Box. I think it is extremely helpful to list briefly the steps CALCULATE performs whenever it is invoked. (So maybe we are not at 10,000 feet, but 5,000?)
The CALCULATE function performs the following operations:
-
Create a new filter context by cloning the existing one. (***Important visual step!***)
-
Move rows in the row context to the new clone filter context box one by one replacing filters if it references the same column. (We will ignore this step for this post)
-
Evaluate each filter argument to CALCULATE in the old filter context and then add column filters to the new clone filter context box one by one, replacing column filters if it references the same column.
-
Evaluate the first argument in the newly constructed filter context.
-
Destroy this newly created, cloned filter context box before moving on to calculating the next “cell.”
If you’re interested in getting started with DAX, this is a good place to begin.