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.
Greg_Deckler is right, by iterating the list of names, the LASTNONBLANK is evaluated for each customer, so you get the last value for each customer within the period considered.
Two suggestions:
- Don't use LASTNONBLANK, it's slow and iterates all the dates while a MAX would have been more efficient
- Look at Semi-additive calculations – DAX Patterns to see an explanation of the best approach depending on the result you want to obtain.
Thank you marcorusso and Greg_Deckler for your great input.
I would also add that this is how ARU_'s formula really looks like
balance1 =
SUMX (
VALUES ( Balances[Name] ),
CALCULATE (
SUM ( Balances[Balance] ),
LASTNONBLANK (
CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ),
COUNTROWS ( CALCULATETABLE ( Balances ) )
)
)
)it contains a couple of nested (but hidden) CALCULATE's.
And I agree to NEVER use LASTNONBLANK it is very slow and there are many alternatives out there.Thanks marcorusso for your prompt response.
I understand that calculate explicit filters are evaluated in original context, while the filteration on the basis of each name happens on account of context transition?
How come then the evaluation of Lastnonblank function takes into consideration the filter on the basis of names?By the way, i am reading your book on DAX 😊 - The definitive guide. Thank you for writing such a great book. Its a privilege to interact with best in class like you marcorusso Greg_Deckler tamerj1
