Forum Discussion
Explicit 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.
In 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.
Thanks 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
RELATEDTABLE = CALCULATETABLE
Which means 'Balances' is calculated for 'Balances'[Name] = "Katie Jordan" only her rows are visible.
I really appreciate your enthusiasm for learning but let me give you an advice as a Mechanical Engineer rather than a Data Analyst. As engineers we like to do things simple and efficient, we rely more on useful practical science and avoid dealing with the less useful complex theory. Don't bather much trying to understand such complex scenarios but always try to simplify your problem and find the fastest and most efficient (cheapest) method to achieve your goals.Hey Thanks tamerj1 - Basically, this explains the piece of information i was missing. To be honest, i did not know that Relatedtable is calculatetable as highlighted by you. I did go back and checked the documentation too.
This teaches me a lesson that i should not assume the working of DAX function and read the documentation (again ) if DAX behaves differently from what i had expected. Thank you.
I would say i really was "obsessed" and enthusiastic both in learning this as i am still half way in my learning-journey of DAX. That is why the hunger of clearing the fundamentals concepts come.
I do agree with you that this was not the most efficient way of achieving the output in this particular case, however efficiency was never the objective in the first place. In the long run, your advise will be helpful to me 😊
I really appreciate your patience in responding to a post which already has an accepted solution. Cheers.
