Forum Discussion
Help me understand Calculate steps
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: