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

user_1779091 0
Viewed 1586

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 if I have understood this concept right and what is the significance/usecase of using either approach.

dax powerbi
2 Answers

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?

user_114054230

Unfortunately, I am not quite sure whether there is any difference in the examples you provide... But I have a "related" issue where the difference is absolutely fundamental in performance...

enter image description here

Where HIDDEN_RAW_SALES_UNIT is a measure scanning a relatively big fact table (scanning ~45 million out of 110 million rows)

enter image description here

The other details of HIDDEN_RAW_SALES_UNIT are probably not very important.

This first version performance is abismal and actually may not come back depending on context (running out of server memory).

The alternative below returns in no time at all.

enter image description here

As far as we can tell, they both return the same result (when the slow one does return)

We are struggling to understand why sucjh a difference in speed...

I can see two differences with your example

  1. we eliminate filters on just one column, not an entire table
  2. we use a measure, rather than a column to aggregate
user_103313510
Related