Is there any sequence on Calculate, Filter & Relationship

Hey guys,
This measure is not working and i don’t understand why.
I’m trying to find out how many hours were not “considered” if my client ID is equal to zero, and i have this table with an inactive relationship with my dates table.

Hours (Not Charged) =
CALCULATE ( [T Tempo],
FILTER ( ‘Tab Time’, ‘Tab Time’[Client#] = 0),
USERELATIONSHIP ( ‘Table Dates’[Date], ‘Tab Time’[Date] )
)

Can you tell why? OR, is there any sequence order for this, like first CALCULATE, 2nd FILTER and so on…
Thank you guys
Pedro

Hi @pedroccamara. If possible, please upload your work-in-progress PBIX file along with a screenshot of what you’re getting now and a marked-up screenshot of your expected outcome to improve the visualization of your issue.
Greg

Thanks for posting your question @pedroccamara. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

Also, please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

Hi @Greg and thank you for your answer.
It’s complicated to add this file cose it’s a 7 table model. Does your answer means that my measure could be done other way? With other functions?
I’m curious because I think there might be a sequence in my measure that I’m not respecting. That’s why I believe this is the reason why it’s not working, unless you could do this measure other way.
Thanks anyway

@pedroccamara refer to this post and if still something isn’t clear then let me know.

Here is the evaluation order of your measure, refer to the comments inside the code:

Hours (Not Charged) =
CALCULATE (
    [T Tempo], -- Fourth Step Evaluate this measure in the modified filter context
    FILTER (
        'Tab Time',
        'Tab Time'[Client#] = 0
    ), -- First Step - This part is evaluated in the filter context outside CALCULATE
	   -- Third Step - Once the relationsip is modified the result of FILTER is applied to the filter context and the 
                       first condition/measure is evaluated.
    USERELATIONSHIP ( 'Table Dates'[Date], 'Tab Time'[Date] ) -- Second Step - This part changes the relationship 
)

2 Likes

Hey @AntrikshSharma. Thanks for replying to this message.
It makes a lot of sense your explanation, and although i didn’t know, i believe my measure is exactly like that.
The visual is a simple graph that has these 2 measures, hours deb and hours not debt by month and year, and the other is a matrix visual by project name and client name. Pretty simple i guess.
In your opinion why do you think it’s not working then?

@pedroccamara I think since you are not removing filters from ‘Tab Time’ using ALL/ALLSELECTED that could be a reason why your measure doesn’t works.

How/where would you add it in the measure?

Here are some variations, last 2 are the same.

 Hours (Not Charged) =
    CALCULATE (
        [T Tempo],
        FILTER ( ALLSELECTED ( 'Tab Time' ), 'Tab Time'[Client#] = 0 ),
        USERELATIONSHIP ( 'Table Dates'[Date], 'Tab Time'[Date] )
    )

.

Hours (Not Charged) =
CALCULATE (
    [T Tempo],
    'Tab Time'[Client#] = 0,
    USERELATIONSHIP ( 'Table Dates'[Date], 'Tab Time'[Date] )
)

.

Hours (Not Charged) =
CALCULATE (
    [T Tempo],
    FILTER ( ALL ( 'Tab Time' ), 'Tab Time'[Client#] = 0 ),
    USERELATIONSHIP ( 'Table Dates'[Date], 'Tab Time'[Date] )
)
1 Like

Hey @AntrikshSharma
Awesome!!! The second measure is the one that is working. The difference between your and mine is the FILTER function, You just took it out. Unbelievable. And i thought that i would have always to use this function in this case…
Thank you so much!!!

1 Like

Awesome! Yeah you are right last 2 are different!!

Also, just so that you know

CALCULATE (
    [T Tempo],
    'Tab Time'[Client#] = 0,
    USERELATIONSHIP ( 'Table Dates'[Date], 'Tab Time'[Date] )
)

is equivalent to

CALCULATE (
    [T Tempo],
   FILTER ( ALL ( 'Tab Time'[Client#] ), 'Tab Time'[Client#]  = 0 ),
    USERELATIONSHIP ( 'Table Dates'[Date], 'Tab Time'[Date] )
)

ooohhhh now you’re confusing me… :grinning:
I understand the 1st but not the 2nd.
I’ve also learn to be aware of the context. I mean, if i’m seing total hours by day it works fine, but if i change the context to clients or month and year, it won’t work (my old measure). This new measure, works no matter the context. I believe, but i’m not sure, tgaking out the FILTER function, makes all the difference. I really know nothing of pbi…
Thank you very much @AntrikshSharma

Actually when you write a predicate or boolean logic like ‘Tab Time’[Client#] = 0 then internally it expands to FILTER ( ALL ( ‘Tab Time’[Client#] ), ‘Tab Time’[Client#] = 0 ).

2 Likes

I have a quick question. I just started reading The definitive Guide to Dax by Marco Russo and things have started to get really confusing.

As you mentioned :
“Evaluates all the filter arguments of CALCULATE in the original filter context that is active outside CALCULATE and then are kept aside”

What does that mean actually? I could use DAX practically but after reading the book, things are getting messed up in my head.

If you could explain this it would really help me a lot.

Thanks!

Here is a calculation:

=
CALCULATE ( [Total Sales], FILTER ( Products, Products[Color] = "Red" ) )

Let’s say you call this measure in a table visual where on the rows you have Brands

Before CALCULATE starts its job what is the filter context of a cell? Let’s say it is Products[Brand] = “Contoso”

Now CALCULATE starts, it will evaluate FILTER ( Products, Products[Color] = “Red” ) in the filter context outside CALCULATE that is Contoso and all the columns of Products that are for Contoso and Red would be kept aside for a while.

CALCULATE makes a copy of the original filter context to make a new filter context with all the filter arguments evaluated in the previous step and the modifiers that it will evaluate later.

Then context transition happens only if CALCULATE is invoked in a row context, either in a calculated column or in an Iterator function.

Then the calculate modifiers such as ALL/ALLSELECTED/KEEPFILTERS/CROSSFILTER etc are applied to modify the effect of context transition or change the behavior of relationships or how a single filter argument of CALCULATE interacts (KEEPFILTERS is used in this case) with filter context outside CALCULATE .

and once CALCULATE modifiers have done their job, the filter arguments that were evaluated in the first step are applied to the new filter context and then a measure is evaluated. i.e Products[Brand] = “Contoso” & Products[Color] = “Red” would be applied to the new filter context.

2 Likes

Well that explains a lot. Maybe you should write a Guide to The definitive Guide to DAX :smile:

Thanks for the easy-to-understand explanation :+1:

2 Likes

Hahaha the explanation you see above is all because of that book, just keep reading again and again and some day every thing will just come together and start making more sense, it happened with me.

If you are new to DAX focus your attention on Evaluation context, Context Transition, Calculate modifiers, Expanded tables. Once you understand the theory everything else is just about experience.

1 Like

This is what I am doing now. I just keep going back and forth to understand the concepts clearly.

Just finished with Context transition and Calculate chapter and my head is spinning already. Will have to read it again though.

Thanks for the help. :+1:

1 Like