Search code examples
powerbidaxdata-analysispowerbi-desktopmeasure

What is the difference between SUMX(ALL...) vs CALCULATE(SUMX.., ALL..)?


Following are 2 measures:

SUMX ( ALL ( SALES ) , SALES[AMT] )

CALCULATE ( SUMX ( SALES, SALES[AMT] ), ALL (SALES) )

Similarly for the following 2 measures:

SUMX ( FILTER ( SALES, SALES[QTY]>1 ), SALES[QTY] * SALES[AMT] )

CALCULATE ( SUMX ( SALES, SALES[QTY] * SALES[AMT] ),  FILTER ( SALES, SALES[QTY]>1 ) )

Both above examples clear the natural filters on the SALES table and perform the aggregation.

I'm trying to understand what is the significance/use case of using either approach maybe in terms of logic or performance?


Solution

  • The first uses a table function to return the whole sales table and then iterate. The second iterates over the sales table in the context of calculate which removes any filters that were present on the sales table.

    SUMX ( ALL ( SALES ) , SALES[AMT] )
    
    CALCULATE ( SUMX ( SALES, SALES[AMT] ), ALL (SALES) )
    

    In these two DAX functions, ALL() is doing two very different things and it is unfortunate the same name was used. In the first one, ALL() is being used as a table function and returns a table. In the second one, ALL() is being used to remove filters and could be replaced with REMOVEFILTERS() (the first one cannot be replaced this same way).

    This is a lengthy and detailed topic and I suggest you make a cup of coffee and have a read here: https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

    To summarise the article, ALL() and REMOVEFILTERS() are not the same. ALL() can be used where REMOVEFILTERS() is used but not vice versa.

    CALCULATE ( SUMX ( SALES, SALES[QTY] * SALES[AMT] ),  FILTER ( SALES, SALES[QTY]>1 ) )
    

    This DAX uses calculate to change the filter context and remove any existing filters. The important thing is that it is removing existing filters.

    They mainly achieve the same result (most of the time) but there is still more nuance though. In DAX, there are always multiple ways of achieving the same outcome. More importantly, DAX is always dependent on the evaluation context. Writing SUM(SALES[AMT]) can return different numbers depending on context. If it was in table with colour, it would return the sum per colour at each line and a total. If it were by country, it would return a total by country and a total. i.e. the exact same formula returns different results depending on context. In this simplistic example, they are essentially the same though.

    The second example would also never be written it this way as you should never filter entire tables (especially fact tables). You would filter the column instead. e.g.

    SUMX(
    FILTER(VALUES(Sales[Quantity]), 
    Sales[Quantity]>1), Sales[Quantity] * Sales[SalesAmount]
    )
    

    This whole video is an excellent watch but if you watch from 45:33, you can see a good explanation of the difference between removing filters and returning a table which is the essence of your question. You also need to understand expanded tables which is explained earlier in the video. youtube.com/watch?v=teYwjHkCEm0&list=WL&index=2