topic Re: Help me understand Calculate steps in DAX Commands and Tips

Help me understand Calculate steps

Zx2000 — Tue, 24 Jan 2023 17:49:17 GMT

Hello,

I am trying to learn DAX and many things there are a real mystery which leads to rael frustration. At this stage Ii am trying to understand steps for CALCULATE from a DAX Bible, however there are many terms that are used there which are unclear in each step (like smth is applied or evaluated...ehh) . I have a dax code as below and I am trying to figure out why it gives particular result. The matrix in report uses colors as rows from product[color] ( so it creates initial outer filter context). Could somebody tell me what is done in each step od Calculate alogirthm. I though I had understood it but...nope.

test 1 =
CALCULATE (
[Sales Amount],
KEEPFILTERS (
FILTER (
ALL ( 'Product'[Color] ),
'Product'[Color] = "Red"
)
),
ALL ( 'Product'[Color] )
)

Re: Help me understand Calculate steps

tamerj1 — Tue, 24 Jan 2023 19:23:38 GMT

Hi Zx2000
The filter argument ( FILTER ( ALL ( 'Product'[Color] ), 'Product'[Color] = "Red" ) is first evaluated.

Then the CALCULATE modifiers KEEPFILTERS and ALL are applied over the outer filter context therefore, the outer filter will be kept all. In other words each color in the outer filter context will become all the colors and it will not be replaced by the inner filter rather it will be intersected with it. Apparently, the intersection between all the colors and "Red" will result in "Red" everywhere.

Re: Help me understand Calculate steps

Zx2000 — Tue, 24 Jan 2023 21:37:44 GMT

Thank you for your help.

1. What does it mean that is being evaluated? Does it give a table in particular outer filter conditions and is kept without any other further influence? Like some kind of constant (color = red)?

2.The book states that KEEPFILTERS is not a calculate modifer but argument modifier. I thought that the whole : KEEPFILTERS (FILTER (ALL ( 'Product'[Color] ),'Product'[Color] = "Red") is being evaluated in outer filter context as a argument, so it gives ie. BLANK in matrix's row where the "Silver" and "Red" where "Red" is. So I was sure this is the way context is being ctreated.
Does it mean that KEEPFILTERS here is used only to intersect with other filters "made inside calculate and by calculate"? So in this case it is only intersect with result of ALL? And this ALL is modifing the outer contex by removing filters from color?

I guess these questions I am asking are just top of the iceberg but I really think that understanding Calculate is important part before next step into DAX and these CALCULATE steps described in DAX Bible are very general and hard to understand. I think it is only understandable by an experienced person who wrote or create DAX:) I can't find other sources.

Re: Help me understand Calculate steps

tamerj1 — Wed, 25 Jan 2023 07:33:39 GMT

OwenAuger, CNENFRNL, Greg_Deckler
Are the top active members in the community who have the ultimate understanding of the evaluation contexts. Hopping any of them having the time explain this behaviour of CALCULATE.

Re: Help me understand Calculate steps

tamerj1 — Wed, 25 Jan 2023 07:52:08 GMT

Good morning Zx2000

Actually your question is extremely important and it is at the core of DAX evaluation concepts. This in-depth understanding of the order of evaluation is the basic starting step towards professional usage of DAX.

Yes when a filter is evaluated, a table is actually calculated. This happens independently for each calculation point (a matrix cell for example). So consider a particular cell in table visual, the outer filter context would consist of the filters coming from rows, columns, slicers, page filters etc.. This out filter is actually tables.
When using CALCULATE to filter (using Boolean condition) a column that pre-exists in the outer filter context, for example:

CALCULATE ( [Sales Amount], 'Product'[Color] = "Red" )

Then the expression is translated into

CALCULATE ( [Sales Amount], FILTER ( ALL ( 'Product'[Color] ), 'Product'[Color] = "Red" ) )

Because CALCULATE wants to consider ALL the colors while calculating the filter table which sound to be convenient despite being a little confusing. If you want to only consider the VALUES of colors that do exist in the outer filter context then you need to manually do it as follows:

CALCULATE ( [Sales Amount], FILTER ( VALUES ( 'Product'[Color] ), 'Product'[Color] = "Red" ) )

As VALUES function does not ignore the outer filter context and CALCULATE filter table will be pre-filtered based on the outer filter context.
In both cases, the next step would be by replacing the outer filter table with the new filter table "Only for the the column(s) stated inside CALCULATE"
Now going back to our matrix cell, and let's say that the outer color filter of this cell is "Blue" then:
- In the first example (using Boolean or ALL), we are starting from ALL colors, then we are filtering only "Red", therefore, the inner filter table now contains only "Red" and the outer contains only "Blue". The inner filter now replaces the outer filter i.e. "Red" replaces "Blue" and we have now only "Red". Then the expression [Sales Amoun] is evaluated filtered to the color "Red" only.
We can go one step further and ask the engine not to replace the outer filter with the inner filter but to keep it and thus the two filters will be merged together (intersected). This can be achieved using KEEPFILTERS

CALCULATE ( [Sales Amount], KEEPFILTERS ( 'Product'[Color] = "Red" ) )

In this case, the outer filter "Blue" will be intersected with the inner filter "Red" and the intersection is just an empty table, thus the expression [Sales Amount] returns blank.

- In the second example (using VALUES) we are starting from the current filter filter context which contains only "Blue". This table that contains only "Blue" is filters to only "Red" which results in an empty filter table that is then applied over the expression [Sales Amount] and the result would be a blank.

I don't think so. KEEPFILTERS is a local modifier but that doen't mean it forces the evaluation of the filter argument within the outer filter context. Otherwise, it would be exactly the same as the (VALUES) expression but its not and here is my proof using the very same approach of yours:

Re: Help me understand Calculate steps

CNENFRNL — Wed, 25 Jan 2023 08:24:28 GMT

tamerj1 thank you for the compliment, I'm really flattered!

Zx2000

Above all, unlike Excel formula, DAX measures are super, super, super ... sensitive to all elements in its evaluation context; literally means, any element (relationship, row/column, slicer ...) does impact on the calculation.

As for your measure, I can't comment more than just simply it this way now, given that there is no data model available.

test 1 = CALCULATE( [Sales Amount], KEEPFILTERS( 'Product'[Color] = "Red" ), ALL( 'Product'[Color] ) )

Re: Help me understand Calculate steps

Greg_Deckler — Wed, 25 Jan 2023 14:49:02 GMT

Zx2000 First, let me state that trying to learn CALCULATE when first learning DAX is a terrible, terrible idea. In fact, CALCULATE is mostly for suckers who enjoy exploring the ultimate depths of rabbit holes of sadness and despair. 90% or more of the things you can do with CALCULATE you can do without CALCULATE. So, just learn this pattern:

VAR __Table = 'Table'

VAR __Table1 = FILTER(__Table, <some filter>)

VAR __Result = SUMX(__Table1, <some column>)

Table, Filter (or group or FILTER and group), X aggregator, done.

That basic pattern will allow you to solve the vast, vast majority of DAX calculations without ever using CALCULATE. In fact, I am firmly convinced that CALCULATE really only exists to give sweats and try hards a reason to post stupid "what does this crazy CALCULATE formula do" challenges. It's embarrassing.

But, specific to the question at hand, this:

test 2 = CALCULATE(SUM('Reseller Sales'[SalesAmount]), KEEPFILTERS('Product'[Product Color] = "Red"))

Would return a value for only Red color in the Product table. Other colors would be blank

This:

test 3 = CALCULATE(SUM('Reseller Sales'[SalesAmount]), ALL('Product'[Product Color]))

Would return the same sales amount number for all colors and the sales amount would be the total sales amount for all colors.

Your measure:

test 1 = CALCULATE(SUM('Reseller Sales'[SalesAmount]), KEEPFILTERS('Product'[Product Color] = "Red"), ALL( 'Product'[Product Color]))

Gets all the colors, then only keeps the color red which means that it will end up with all colors having the same value which will be the total value for Red only.

Now ask yourself, why do you need that? So that people can write blog articles and books explaining this stupid behavior.

Re: Help me understand Calculate steps

Zx2000 — Wed, 25 Jan 2023 22:26:24 GMT

Thank You for Your answer, I am a kind of person who still want to understand as much as possible:)

Re: Help me understand Calculate steps

Greg_Deckler — Wed, 25 Jan 2023 23:29:52 GMT

Understood, you just have to memorize this from sqlbi then:

CALCULATE evaluation follow these steps:

CALCULATE evaluates all the explicit filter arguments in the original evaluation context, each one independently from the others. This includes both the original row contexts (if any) and the original filter context. Once this evaluation is finished, CALCULATE starts building the new filter context.
CALCULATE makes a copy of the original filter context to prepare the new filter context. It discards the original row contexts, because the new evaluation context will not contain any row context.
CALCULATE performs the context transition. It uses the current value of columns in the original row contexts to provide a filter with a unique value for all the columns currently being iterated in the original row contexts. This filter may or may not contain one individual row. There is no guarantee that the new filter context contains a single row at this point. If there are no row contexts active, this step is skipped. Once all implicit filters created by the context transition are applied to the new filter context, CALCULATE moves on to the next step.
CALCULATE evaluates the CALCULATE modifiers used in filter arguments: USERELATIONSHIP, CROSSFILTER, ALL, ALLEXCEPT, ALLSELECTED, and ALLNOBLANKROW. This step happens after step 3. This is very important, because it means that one can remove the effects of the context transition by using ALL as a filter argument. The CALCULATE modifiers are applied after the context transition, so they can alter the effects of the context transition.
CALCULATE applies the explicit filter arguments evaluated at 1. to the new filter context generated after step 4. These filter arguments are applied to the new filter context once the context transition has happened so they can overwrite it, after filter removal — their filter is not removed by any ALL* modifier — and after the relationship architecture has been updated. However, the evaluation of filter arguments happens in the original filter context, and it is not affected by any other modifier or filter within the same CALCULATE function. If a filter argument is modified by KEEPFILTERS, the filter is added to the filter context without overwriting existing filters over the same column(s).
The filter context generated after point (5) is the new filter context used by CALCULATE in the evaluation of its expression.

CALCULATE – DAX Guide

Zx2000

Re: Help me understand Calculate steps

Zx2000 — Thu, 26 Jan 2023 08:28:09 GMT

I know these steps. I tried to study them hard:) It is not problem to memorize this but to understand. Some points or parts of points without many examples are not understandable for me and many terms used there may be understood in different way in my opinion (4 and especcially 5) . Many times I was sure that i finally understood meaning of these steps but at the end with some particular example (code which gives different result then expected) It turned out that my understanding was incorrect. So every time I tried to understand it once more in a different way

Re: Help me understand Calculate steps

Anonymous — Sat, 02 Dec 2023 13:30:53 GMT

Hi,

Alberto in their Mastering DAX course said that KEEPFILTERS is not CALCULATE modifier therefore if there is second argument using ALL then ALL is used first because it is a modifier. Then in one of their page I read that KEEPFILTERS is CALCULATE modifier.

So which is it?????????