Summarize VS Summarizecolumn function in DAX

[Anonymous]

Hi,

Actually i am confused with Summerize and Summerizecolumn functions.

Can somebody explain it in easier words with example.

I have read sqlBi blog which is preety good, i am not able to understand why microsoft has released it since we have summerize function already.

I only understood in summerizeColumns have better performance and SUmmerizecolumns will apply filter context later after cross join and we can't use same column twice in summerizecolumns.

Please somebody tell me majot advantages of using summerizecolumns over summerize function.

Thanks,

Pravin

[v-gizhi-msft]

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

[Anonymous]

Before posting quetion here i already checked SQLBI blog.

So as i can see you got the same result with just syntax change in SUmmerizecolumn and Summerize with addcolumns.

I am still curious is there any major  differance other than performance?

Thanks,

Pravin

[v-gizhi-msft · Accepted solution]

Hi,

Typically, it is recommended to use SummarizeColumns to add columns rather than Summarize.

Best Regards,

Giotto Zhi

[Anonymous]

Hi @v-gizhi-msft 

I know it is recommended i am asking reason behind it. is it only for performance or something else?

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

[v-gizhi-msft]

Hi,

SummarizeColumns can use Ignore function but summarize ca not.

Reference:

https://dax.guide/ignore/ 

Best Regards,

Giotto Zhi

[Anonymous]

Thanks @v-gizhi-msft 

Yes i have checked Ignore function with Summarize it is throwing error. You can use it with SUmmarizeColumns only.

So the conclusion is we can omit blank from measure output without doing any furtherstep in SUmmarizecolumn.

and other is it has better performance than Summarize.

Thanks,

Pravin