Forum Discussion

ARU_'s avatar
ARU_
Advocate I
3 years ago
Solved

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

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 balanc...
"CALCULATE"
"DAX"
context transition
Explicit measure
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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.

marcorusso's avatar
marcorusso
Most Valuable Professional
3 years ago

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_'s avatar
    ARU_
    Advocate I
    3 years ago

    Thank you marcorusso . I will refer these videos. 

  • ARU_'s avatar
    ARU_
    Advocate I
    3 years ago

    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