CALCULATE Mantra

DAX DAX Calculations
1

Here are some steps that I recite when I am stuck with a complex problem, it is always good remember CALCULATE’s execution order.

  1. Evaluates all the filter arguments of CALCULATE in the original filter context that is active outside CALCULATE and then are kept aside

  2. CALCULATE creates a new Filter by copying the original filter context

  3. If it is called in a Row Context, either by an iterator or Calculated column then it takes that particular row and create an equivalent filter from values of each column in that row and then add it to the new filter context created in the second step. Otherwise this step will be skipped

  4. Modifiers such as ALL, ALLSELECTED, ALLEXCEPT, ALLNOBLANKROW, CROSSFILTER, USERELATIONSHIP, KEEPFILTERS etc. are evaluated so that they can alter the effect of context transition if required

  5. The filter arguments that were evaluated in step 1 are applied to the new filter context

  6. It executes the first argument in the newly created filter context

6 Likes
2

@AntrikshSharma,

Great stuff - I really appreciate the tips you’ve been posting.

If I can make a suggestion on this one, I think a visual example would be really helpful. Many of us on the forum use SnagIt as our screen capture program, and one of the many great features it has is the ability to number steps, per my screenshot below. Thus, an example measure with the numbers corresponding to your execution order steps above I think would make this even more useful.

Thanks again for your contributions – great to have you here.

  • Brian

image
image872×592 20.4 KB

3 Likes
3

Thanks a lot Brian, that’s a great suggestion, and of course I will do and will add one to this too!!! :smiley:

4

Great post. Love these ideas and your thought process

1 Like
5

Hi @AntrikshSharma, thank you for providing this group with great tips. We greatly appreciate your efforts and your share of knowledge.

1 Like