Summarize VS Summarizecolumn function in DAX

Hi,

The difference is that Summarize function has several pitfalls and performance issues, and for compatibility issues it cannot be fully fixed by Microsoft.

For example, here is test table:

if you want to summarize [Product] and [Sales]*[Unit Price] as [Profit] column, maybe you will use this:

Summarize Table = SUMMARIZE('Table','Table'[Product],"Profit",SUMX('Table','Table'[Sales]*'Table'[Unit Price]))

But this is not correct though it shows well.

You should use this instead of the above:

Summarize Table-2 = ADDCOLUMNS(SUMMARIZE('Table','Table'[Product]),"Profit",CALCULATE(SUMX('Table','Table'[Sales]*'Table'[Unit Price])))

If you use Power BI, Analysis Services 2016, or Excel 2016(*), you can use a new DAX function called SummarizeColumns which apparently is just a replacement of Summarize:

SummarizeColumns Table = SUMMARIZECOLUMNS('Table'[Product],"Profit",SUMX('Table','Table'[Sales]*'Table'[Unit Price]))

When you compare the query plans of the two versions using DAX Studio, you will notice that the SummarizeColumns is extremely efficient, requiring just one storage engine query and a smaller number of steps in the physical query plan.

Reference:

https://www.sqlbi.com/articles/introducing-summarizecolumns/ 

Hope this helps.

Best Regards,

Giotto Zhi