Forum Discussion
Advocate IExplicit filter argument of Calculate function : Not able to understand its evaluation
ARU_ Since marcorusso wrote the explanation for CALCULATE's internal wiring, perhaps he might drop by and explain where you are going wrong in your interpretation of things. However, I believe where you might be not understanding things is that the CALCULATE expression is being evaluated for each row within the SUMX. It is not independent and does not come "first". Thus, for Katie Jordon, when the CALCULATE determines the LASTNONBLANK, it is finding the LASTNONBLANK within the context of Katie Jordon. The same is true for the other individuals. It is not that the LASTNONBLANK fires and returns the last date where the COUNTROWS is blank for ALL of the people, it is always scoped to each individual. Hope that makes sense.
Most Valuable ProfessionalIn my previous reply I wrote this:
As I said, you might be confused by the fact that LASTNONBLANK performs a context transition, which wouldn't happen with other approaches (the CALCULATE context transition only applies to the first argument, LASTNONBLANK is not involved by that context transition and generates its own - look at the documentation on DAX Guide).
Let me rephrase: the filter arguments of CALCULATE are executed in the original filter context; however, LASTNONBLANK performs a context transition on its own as described in LASTNONBLANK – DAX Guide
I hope it helps.
Advocate IThanks marcorusso for the lucid explanation and your patience. I understand that the iterative process on names triggers context transition for the first argumnet of "LASTNONBLANK" function .
I am sorry, but still i am not able to make sense of Grand total computation as yet.
If i could represent my understanding below with the first iteration of the name "Katie Jordan". This is how i think the context transition is happening (under-lined with Red)
Now, the inner calculatetable function filter on account of context transition do not have any impact as Balance table has many to one relationship with Date table. So, it will yield all the dates present in the Date table even with "Katie Jordan" filter.
With 2nd argument of LASTNONBLANK function, when it checks for related records, it should invariabily output "18th July 2010" as the lastnonblank day even for Katie Jordan.
On another note, the filter argument of outer calculate function is also a result of context transition. As this is an implicit filter and hence it should not have any bearing on the explicit filter (LASTNONBLANK function) working which is entirely based on original context evaluation.
Somehow this DAX is filtering the date at an individual person level, which i fail to understand why.
Thank you so much