Optimizing nested iterators in DAX

Possible optimization approaches to improve the performance of nested iterators in DAX. Article and download: sql.bi/70755/?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin...

[@AgulloBernat]

I can't belive these awesome videos don't even have ads,

[@lukeminikoski]

It's self-ads!

[@Wzxxx]

Soon they will

[@SQLBI]

We have no plans to add ads. After all, we already promote our courses! www.sqlbi.com/training/

[@AgulloBernat]

@@SQLBI great courses, by the way

[@Wzxxx]

@@SQLBI Honestly i am not against any ads especially when people share their knowledge which they collect for years.

[@agasti2007]

I like your video even before watching it. SQLBI is my favourite thing on internet. - a DAX and MDX fan.

[@diegomsg]

So much clarity presenting complex solutions with simple approaches.

[@lukeminikoski]

Watching this kind of videos, I'm pretty sure theat video courses and the books are the best investments I ever made! Thank you!

[@omalinsky]

thank you, Alberto this video goes so well with reading the chapters on calculation context, Calculate, and context transition in the Definitive Guide to DAX!

[@viniciusfernandes3552]

Thanks Alberto.

[@lmblancoancos]

Thank you Alberto, the whole video is great, but the final trick, when you change the code to made it work with only one iteration is fantastic

[@jiajinliang6121]

Thank you Alberto for such a clear and thorough lecture! It is just awesome

[@mnowako]

Unbelievable useful lesson. Thank you, professor Alberto.

[@JontyLP]

Great video, awesome that you share your knowledge openly like this!

[@smartdata678]

Ciao Alberto! I love your: This is always dangerous! :) Good video as always!

[@ramonillarramendi3191]

Amazing!! Excellent video and explanation. Thank you very much

[@satishkumardommeti8282]

Simply awsome. You are indeed a DAX Super Guru.

[@Milhouse77BS]

Thanks. Makes me feel need to rework my longer running DAX queries.

[@Bharath_PBI]

Enjoying DAX! Thank you 👍

[@pratikfutane8131]

Thanks a lot Alberto! Great video!

[@3danim8r1]

That's beautiful! Really amazing

[@wodraeve]

Thanks. I would be interested in seeing a few videos on how to deal with 'time'. What if your granularity is at a second level? How does this affect your model and your measures? Splitting date and time or both? Etc...

[@danjarupath]

Awesome !!! Appreciate that you contribute a lot to all over the world world 🌍🌍🌍🌍🌍

[@tibobago]

Thanks Alberto, another great video. Super interesting to see that you can actually use nested iteration by adding granularity and in your example it was super quick. I have the perfect dataset to test all that tomorrow!

[@RobertoStaltari]

Pure gold. Thanks

[@arifismayilli6936]

Perfect !!!

[@101Kotetsu]

Buen trabajo!

[@kadirkkkk]

I stuck on nesting 4 level hierarchy and sum/average based on row parameter. Could not find an exit so now turning back to power query to handle this. sorry dax

[@benjison1000]

What about if the 2 two tables that are used are Many to Many? It looks like in this example product/customer are on the 1 side, so sales can be used. In my case I need to look at both table1 and table2. Both table1 and table2 have millions of rows. i.e. SUMX( Table1, --many side SUMX( FILTER( Table2, -- many side Table2[BuyStatus] = 3 && Table2[ClientKey] = Table1[ClientKey] && Table2[DebtorKey] = Table1[DebtorKey] && (Table2[TransPostDate]=(Table1[MonthEnd2]-30)) && (Table2[CloseDate]>'Table1'[MonthEnd2] || ISBLANK(Table2[CloseDate])) ), Table2[Amt] ) )

[@questionsformedb8157]

Super good

[@lanasedayanoch]

The best lecturer in the world! Just one question (not sure if you do answers) - why Performance Analyser and Dax Studio server timings had 25% difference in speed?

[@SQLBI]

Every run could be different, but DAX Studio does not include the time required to fetch the result, which is probably included in Performance Analyzer.

[@DIGITAL_COOKING]

please question: if I said that I understand clearly the video (beside your high quality of teaching of course 🙂) does it mean that I grab some basics about DAX concepts?

[@SQLBI]

Yes you should!

[@visheshjjain]

Why did we use variables inside the SUMX() and not just use the respective columns?

[@SQLBI]

No differences, by using variables the code is self-documented (the variable name describe the purpose of the sub-expression).

[@paragkhandalkar]

Can you please create something on dynamic currency conversion in powee bi..we use sap bw data in power bi..and we yet do not have solution for dynamic currency conversion.. please can you create a video on that..will be very usefull

[@SQLBI]

It's already available here: www.daxpatterns.com/currency-conversion/

[@NoShadowOfDoubt1]

Hello Alberto, I have some questions: 1- are these KZbin videos same videos that you offer in the course? 2- will you cover DAX excel solutions in Excel in the course, for an example, selected value and remove filters don’t exist in power pivot, ? Thank you

[@SQLBI]

No - the video courses have different videos and also include exercises. The course is a more structured approach to basic concepts and detailed analysis of syntax/use/optimization (depending on the video course). The videos on KZbin are focused on single issues. The second question is not very clear - the Mastering DAX video course is a course about the DAX language that you can use with different tools. The examples and the exercises use mainly Power BI, but the language is the same across all the platforms.

[@Th3M0rri]

nice to see a mechanical keyboard :) What switches are in there?

[@SQLBI]

You can find the hardware used in the videos here: www.sqlbi.com/blog/alberto/2021/02/14/on-my-recording-gear/