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

Question

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?

Hayton

Answer 1

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

Antagonize

But isn't the logic same for both? – Hayton

ALL() and REMOVEFILTERS() are not the same at all. ALL() can be used where REMOVEFILTERS() is used but not vice versa. It is a difficult topic to understand but I encourage you to read the link as they show lots of examples of the difference between removing filters and using ALL() as a table function. – Antagonize

The second one uses calculate to change the filter context and remove any existing filters. The important thing is that it is removing existing filters (no option to bold existing in comments) – Antagonize

Effectively both formulas work upon an unfiltered Sales table and compute the SUM. Am I wrong? – Hayton

Is that correct? – Hayton

Also please see the 2nd example from my question - FILTER – Hayton

Yes, that's correct but there is still more nuance though. In DAX, there are multiple ways of achieving the same outcome. In addition, 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. – Antagonize

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. – Antagonize

Your second example is similar but you would never write 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]) – Antagonize

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 – Antagonize

Can you update the answer to cover both the measures from my question – Hayton

Do you mean with the comments I have already written? – Antagonize

Yes that and whatever more really so that it's useful to the person who reads the answer.. – Hayton

Got it. I'll update now. – Antagonize

For CALCULATE ( SUMX ( SALES, SALES[QTY] * SALES[AMT] ), FILTER ( SALES, SALES[QTY]>1 ) ), why did you quote that This DAX uses calculate to change the filter context and remove any existing filters.? Where exactly is it removing any existing filters? – Hayton

I think it meant that CALCULATE is causing any filter on SALES[QTY] to be replaced. i.e. whatever was there is removed and then replaced with >1 – Antagonize

Answer 2

In DAX you can achieve the same results from different DAX queries/syntax. So based on my understanding both the DAX provide the same result :

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

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

And the 1st one is a more concise way to achieve way rather than the 2nd one in all cases/scenarios. Currently when I tested these out with <100 records in a table ; the performance was the same for both the measures.

But ideally the 1st scenario would be quicker then the 2nd one which we can test out by >1 million record through DAX studio.

Can you please share your thoughts on the same?

Apiary

Hey @variable, Is my understanding same as yours ? – Apiary

Please can you answer regarding both formulas in original question – Hayton

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. So don't you think the second will be quicker? – Hayton

Answer 3

At the risk of stating the obvious, you are wrapping a function (SUMX) inside a process represented by CALCULATE function.

It is an actual process, which will attempt a context transition.

Beyond the performance implications of forcing extra processing, the answer to your question heavily depends on how and where these measures get injected into the model, as it determines if the context transition would occur.

For reference, here are just some of the relevant SQLBI articles: https://www.sqlbi.com/articles/introducing-calculate-in-dax/ https://www.sqlbi.com/articles/understanding-context-transition-in-dax/

Citrin