Forum Discussion

Zx2000's avatar
Zx2000
Icon for Advocate I rankAdvocate I
3 years ago

Help me understand Calculate steps

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

10 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

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

    • CNENFRNL's avatar
      CNENFRNL
      Icon for Community Champion rankCommunity Champion

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

       

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable

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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    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:

     

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

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion

      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.

       

       

       

      • Zx2000's avatar
        Zx2000
        Icon for Advocate I rankAdvocate I

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