Mastering DAX Calculation EDNA Training

[DMercier]

In a segment of the training video here is a formula and results demonstrated:

I copied all 3 ways and the results are the same for all 3… wondering why I didn’t get the same results as the training?

[BrianJ]

@DMercier,

It looks like you did all three as measures, not calculated columns. I think @sam.mckay was probably discussing context transition - measures already have the context transition implicitly included, whereas calculalated columns do not, and thus need that additional CALCULATE statement to transition from row context to filter context.

I think if you redo them as calculated columns, you’ll get the same answers as Sam. But your instincts are exactly correct - in any real world case I can think of, these should be written as measures. He’s just doing them as calculated columns to illustrate a point.

@AntrikshSharma has done some amazing deep dive posts explaining this complex concept that are not light reading, but definitely worth working through.

I hope this is helpful.

• Brian

[BrianJ]

@DMercier,

Per my earlier post, here’s our resident DAX Mozart @AntrikshSharma’s posts on context transition:

CALCULATE Mantra

Here are some steps that I recite when I am stuck with a complex problem, it is always good remember CALCULATE’s execution order. Evaluates all the filter arguments of CALCULATE in the original filter context that is active outside CALCULATE and then are kept aside CALCULATE creates a new Filter by copying the original filter context 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 …

Implicit Context Transition

Until today I used to think only CALCULATE and CALCULATETABLE can do context transition but after a post by @michellepace I searched on Dax Guide and other places and I found that some other function implicitly and internally call CALCULATETABLE for context transition. Some of these functions are SAMEPERIODLASTYEAR, LASTDATE, FIRSTDATE, RELATEDTABLE, DATEADD, all the NEXT/PREVIOUS/START/END function family, internally they call CALCULATETABLE ( DISTINCT ( Dates[Date] ) ) example: LASTDATE ( C…

Context Transition - Behind the scenes

If you haven’t been living under a rock then you must be aware of Context transition, one of the most complex topics to understand and master but have you ever wondered, what actually happens behind the scenes? To be honest at least for me the behind the scene stuff is much more clear and easy to understand. Without diving too deep into theoretical aspect, If I have to explain context transition to a layman I had say take every value of all the columns of the current row in which a measure or a…

• Brian