Forum Discussion
Advocate IHelp me understand Calculate steps
Community ChampionZx2000 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.
Advocate IThank You for Your answer, I am a kind of person who still want to understand as much as possible:)
- Greg_Deckler
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.
- Zx2000
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