Optimizing SUMMARIZE by using ADDCOLUMNS - Unplugged #40

Learn why you should use ADDCOLUMNS in DAX to compute aggregations instead of doing that inside SUMMARIZE: performance can be very different!
Read more about the "unplugged" format: www.sqlbi.com/blog/marco/2021...
#unplugged

[@kot23]

Your videos are a wealth of information, it's like unlocking DAX superpowers! Thanks for your efforts, keep it up please.

[@Anthony_Lecoq]

What a master class! Thanks Alberto for sharing those tips and tricks. And your way of reasoning. Very valuable :)

[@user-lm5wb8vi1x]

Finished watching

[@tARasKoni]

Best ever cooking show!
I just try to absorb and repeat way of thinking
Thank you, Alberto!

[@christopherhaas8637]

I love the method of comparing your [Test] measure to the original measure to ensure that the expected result is still obtained. I already wrap my SUMMARIZE() in ADDCOLUMNS(), but this was a wonderful tip to learn!

[@sane7263]

Read that article just a couple of days before, now I am super saiyan🔥🔥🔥

[@Yo-px9xw]

it was amazing to watch how do you analyze a problem and solved it. Please make more videos like this one

[@sarajvo2010]

I like part Test to return zero to check optimisation. As always, simple and great video. Thank you!

[@JohnSullivan1]

Another great video that takes the complex and makes it seem simple and understandable. Thanks Alberto!

[@thiagopacifico9283]

Ciao Alberto,
Thank you for this class.
I really appreciate it.
Thiago Pacifico

[@santoshraghunath8133]

You are the best Alberto !!!

[@viniciusfernandes3552]

Amazing. Thanks for the class. Cheers.

[@johansantacruz6464]

Thanks to help us to improve with each video.

[@kayliedoe2312]

Excellent video as always

[@oliveroshea5765]

Thanks Alberto, Buon Anno

[@gezin_longuevilleosaer4245]

Great video, thanks a lot Alberto!! I had a similar issue and now the performance is so much better and faster.

[@wmfexcel]

What a convincing example! Thanks for the video! :)

[@egorshiyanov1206]

Best explanation as always! Keep up the good work 👍

[@lorenzoladejobi8701]

Very educative! Thank you Sir.

[@hokki28]

Very useful, thanks for this tip!

[@rdg515]

I’d like to see more debugging videos! Nice job!

[@kamilkalecinski4115]

Great video Alberto! I believe it will be very appreciated if you would consider to make more videos with popular incorrect functions usage and obvious (for you, not us!) solutions. I nominate FILTER function as a next example.

[@ramonillarramendi3191]

Wonderful! Thank you very much.

[@sue_bayes]

Like the use of subtracting test measure from original measure to validate results.

[@siddheshamrutkar8684]

Superb...

[@FredericLEGUEN-Excel]

Wow! Impressive course to learn how to optimize a measure 👏👏👏 But I don't understand with you embedded DISTINCTCOUNT inside CALCULATE (and only one argument in calculate)

[@Bharath_PBI]

Thank you

[@tommynguyen4253]

If we use extended columns in Vendor table (not in Order table), would addcolumns still work?

[@MoonShine-bs6cl]

How do i get the date column from fact table to another table which is having only many to many relationship on both the tables, how do i solve this as payment dates have multiple transactions and i have to map with policy number to take the average days

[@aaronrodriguez6833]

Awesome video. Thank you for the knowledge share. One unrelated question. What keyboard do you use? lol

[@SQLBI]

You can find it here: www.sqlbi.com/blog/alberto/2021/02/14/on-my-recording-gear/

[@omalinsky]

13:09 - As you have seen, that was an easy one.

[@Piece101]

Thank you Mr.Alberto. one question, can we further reduce the code by avoiding second variable vSum by putting ( "@PO" > 2 ) in calculate function in Addcolums.
I'm new to DAX, sorry if question is not valid ☺️.

[@wilsonman8661]

Functionally, yes. However, variables also help with code readability (both for other people and for future you).

[@princecena]

I have a quick question. [@PO] is created using ADDCOLUMNS and this column does not have any data lineage. So how this column in being used as a filter in the vTable inside CALCULATE. Is it the case that this column is being ignored while the vTable is used as a filter.

[@SQLBI]

That column is ignored for the filter context because it does not have any data lineage, indeed.

[@giri41]

Hello sir,
I have three tables customers, materials, invoices . There is no direct relationship between customers, materials but through invoice. How can I work on customer attributes with materials please?? Help

[@siddheshamrutkar8684]

Can you make a video on Dedicated and Shared Capacity in power-bi along with the difference.. Which will make life easier for most of the org and even developers like me.. 😊

[@jessamhan]

using SUMMARIZE/ADDCOLUMNS to replace an olden full SUMMARIZE is always a good idea. As far as I remember this was one of the first optimizing hint you and Marco gave us way back. However, doesn't the presence of DISTINCTCOUNT play a substantial role in the suboptimal performance in this case. It sort of inflates the performance result when used with SUMMARIZE doing the aggregations instead of ADDCOLUMNS?

[@SQLBI]

There are several cases where SUMMARIZE does not expose performance issues, such as the case of a simple SUM. However, you should evaluate case-by-case, so as a rule of thumb avoiding it is simpler.

[@NareshOjha]

Or better use SUMMARIZECOLUMNS

[@joanbelmar]

@@NareshOjha I guess that SUMMARIZECOLUMS will not work because it doesn't support context transition.

[@powerbinareal]

Insano @powerbinareal