Explicit filter argument of Calculate function : Not able to understand its evaluation

[ARU_]

Hello Members, 

I have a query with respect to working of calculate filters and its evaluation context. 

Some Background 

I have a sample data. I have a task to compute the closing balance of customers.

I got the desired result in the form of following table matrix.

The cell level and sub-total level results make sense to me except the one highlighted in red circle 

I got this result through following piece of DAX code. 

balance1 = 

SUMX(
    VALUES(Balances[Name]),
        CALCULATE(
            SUM(Balances[Balance]),
            LASTNONBLANK(
                'Date'[Date],
                COUNTROWS(RELATEDTABLE(Balances))
            )
        )
    )

What is my Issue?

My understanding of calculate function is that it evaluates its "EXPLICIT" filter arguments in original filter context. In case of this example. LASTNONBLANK is an explicit filter. 

In case of Grand total (circled in red), their original filter context do not have any active filter context i.e it has all the unfiltered names (Balances[Name]) and all the dates ('Date'[Date]).

If we evaluate LASTNONBLANK function in its original context as explained above, it should return 18th July 2010 as the date which will be an explicit filter argument for Calculate function based on which it will evaluate its expression. 

Now, due to sumx iterator, it creates a row context on values(Balances[Name]) table and results into context transition. In case of context transition, it would put implicit filter of names for each row. 

Explicit filter (i.e the result of LASTNONBLANK function) will be applied on top of the result of context transition. As both the filters are not over-riding, we should get something like this for Grand total

Just to be clear, i am getting the desired result with this DAX computation. Nevertheless, i am posting this question to seek help from this wonderful community as to which step above am i failing to understand?

Supporting Info below 

The configuration of Matrix table is as follows 

I have just two sample tables in the data model which are linked to each other with Data column

Link for PBIX file

https://drive.google.com/file/d/1dt3d6p6fJjszsylM_6jj0sY5NSOIcSzc/view?usp=share_link

My basis of the above question is based on my understanding of the working of Calculate function as documented in dax.guide

Thank you so much everyone for patiently reading and attempting to help here.

Regards

ARU

[marcorusso]

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.

[ARU_]

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

[tamerj1]

@ARU_ 

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. 

[ARU_]

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. 

[marcorusso]

I'm not sure I understand your question.

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).

I suggest that you look at these videos (the first three in the series), they could help in getting another "visual" perspective about these concepts: Series The Whiteboard - SQLBI

[ARU_]

Hi @marcorusso  - As suggested by you, I went ahead and watched the first three videos in the whiteboard series. Thanks for pointing me to that direction.

I am sorry, but i am still not very much clear as to how the DAX code works in this case.

This is the expanded version of DAX measure which computes the closing balances:

balance1 =
SUMX (
    VALUES ( Balances[Name] ),
    CALCULATE (
        SUM ( Balances[Balance] ),
        LASTNONBLANK (
            CALCULATETABLE ( DISTINCT ( 'Date'[Date] ) ),
            COUNTROWS ( CALCULATETABLE ( Balances ) )
        )
    )
)

when you say that the 'LASTNONBLANK' filter argument of Calculate is not impacted by context transition, then how the dates are getting computed for each customer name?

As per the first evaluation step of Calculate function (basis dax.guide) says that explicit filters are evaluated in original evaluation context.

In case of Grand total evaluation, there are no active filter context then how come the dates are getting evaluated at a "name" level? 

I am sure, i am getting somewhere wrong in my understanding of evaluation context of this code : 

I guess once i understand its evaluation context environment, then i should be able to understand the root cause of my problem. 

May i request your guidance here?

Hope i am able to articulate my problem.  

Many Thanks Again

Regards
ARU

[ARU_]

Thank you @marcorusso . I will refer these videos. 

[marcorusso]

@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:

1. Don't use LASTNONBLANK, it's slow and iterates all the dates while a MAX would have been more efficient
2. Look at Semi-additive calculations – DAX Patterns to see an explanation of the best approach depending on the result you want to obtain.

[tamerj1]

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. 

[ARU_]

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 

[ARU_]

hi @tamerj1 - Thanks for your response. 

I basically wanted to understand why my version of DAX doesnt give this result as in your case. My query is more to do with understanding the steps which my DAX code takes to generate the output it gives so that i could fill up the gaps in my understanding. 

[Greg_Deckler · Accepted solution]

@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.

[tamerj1]

@Greg_Deckler 

[tamerj1]

Hi @ARU_ 
How about?

Balance2 = 
SUMX ( 
    TOPN ( 1, Balances, Balances[Date] ),
    Balances[Balance] 
)