Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ARU_
Advocate I
Advocate I

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

 

ARU__1-1682512875673.png

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

 

ARU__5-1682514217200.png

 

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 

ARU__3-1682513125337.png

 

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

 

ARU__2-1682513069880.png

 

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

 

ARU__7-1682514904429.png

 

 

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

 

 

Regards

ARU

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

14 REPLIES 14
marcorusso
Most Valuable Professional
Most Valuable Professional

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)

ARU__0-1682698194845.png

 

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

 

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

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
Most Valuable Professional
Most Valuable Professional

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

 

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.

ARU__1-1682587602939.png

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 : 

ARU__0-1682587419855.png

 

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

Thank you @marcorusso . I will refer these videos. 

marcorusso
Most Valuable Professional
Most Valuable Professional

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

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 

 

 

ARU_
Advocate I
Advocate I

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
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
tamerj1
Super User
Super User

tamerj1
Super User
Super User

Hi @ARU_ 
How about?

3.png

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors