Using GENERATE and ROW instead of ADDCOLUMNS in DAX

Improve DAX queries by using GENERATE and ROW instead of ADDCOLUMNS when you create table expressions.
Article and video: sql.bi/54337?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin...

[@anthonypcheng3167]

One of the best tip of DAX function/code. Cool. Love it.

[@Bharath_PBI]

Thank you. Great work as usual. Enjoying DAX 👍

[@BernatAgulloRosello]

When I grow up I want to be like Alberto Ferrari 😁

[@DesertTOON]

Being able to read my code after weekend is hard enough let alone 6 months! Great content BTW

[@sachin.tandon]

Very, very well explained!

[@sundarun]

Fantastic Alberto👏🏾

[@pratikfutane8131]

Great video filled with best practices !! Thank you!

[@AnalyticswithNags]

This is crystal clear explanation. One thing I have in mind for long is that this script basically creating a calculated table using DAX and same thing can be achieved in power query editor as well. So, which one better interns of performance and when to choose power query or DAX to create calculated table.

[@SQLBI]

It depends. If you only have row-level calculations that don't depend on reading/aggregating data on other tables, Power Query is an option (if you are building a Power BI model). However, with composite models coming soon, the scenario could be very different (calculated table based on a remote dataset - you cannot use Power Query in this case).

[@joaquimcosta952]

Very very good like always !!!! Thanks for another good material.

[@vivekgupta309]

all your video series are very good. thanks a lot !!!

[@NirRobinson]

Great video. Everyone must be open to make changes in the model..

[@shekharkumardas]

Thanks for great explanation

[@tonirapture6440]

Hi Alberto/SqlBi Team, I love how you explain things and make this understandable to us newbies in dax. Ive been watching sqlbi tutorials for the past 2 months now and it helps me a lot with my builds. Great stuff as usual! Appreciate you guys effort on sharing your knowledge. By the way, is it possible to do ranking function in the RETURN ROW columns?

[@marilenadonofrio9787]

Wow! now I'll pick up and rethink in this way all the code I've written so far...

[@excelisfun3903]

Thanks, Alberto, for the VERY helpful video : ) : )

[@questionsformedb8157]

This is beautiful

[@HeliSal700]

Nice approach. I will look at it as it next time. In addition you could have mention comments inside the code to describe what happens. I always recommend comments to my customers and my students, as they help to understand what's happening and why I have written the code in that way.

[@SQLBI]

Completely agree, this is what we usually do - However, in videos we have different requirements, like using larger fonts to keep code visible, so we might have to skip comments there. The code is at the service of the explanation in the video.

[@diegomex85]

Ty Guys =)

[@maheshk6916]

Explaining nicely like teaching alphabet to a kid

[@MrKarimanal]

You are my man

[@soy34mb]

Hi, great video as always. Thanks!. How much was improve the efficiency of addcolumns vs generate+row??

[@SQLBI]

As usual... it depends! It's always hard to set a general rule. As a rule of thumb, use GENERATE+ROW for complex dependencies that would add too many nested ADDCOLUMNS. If performance is critical, run your own benchmark, otherwise use the technique that is easier to read.

[@gulhermepereira249]

0:35 "some other people will work on it" = yourself the next morning

[@user-ze4xc6tu4f]

awesome

[@luuminhvuong]

can we apply this for Composite Model

[@DanielADamico]

Excellent video again! I have one doubt though: Is "Generate" better than the other approaches from a performance point of view? Or the real advantage is really on the readability and code maintenance side of things?

[@SQLBI]

When it comes to performance, the answer is always "it depends". Read similar questions we just replied to in this video. Short version: too many nested ADDCOLUMNS are not a good idea. GENERATE for a couple of columns to add without dependencies between them could be a bad idea, too. However, give priority to code readability, then look at performance running your own benchmark, performance may vary and depend on many other factors.

[@DanielADamico]

@@SQLBI Thank you so much!

[@alirezamogharabi8733]

Thanks a lot, I have a question: Date fields in other calendars like Hijri or Persian dates doesn't show in correct format in the reports. How to fix this issue?

[@SQLBI]

We don't have experience on that. Can you point to (possible English) documentation about these issues so we can investigate? Thanks!

[@alirezamogharabi8733]

@@SQLBI Yes, I will send you a comprehensive document about this issue.

[@SQLBI]

Thanks!

[@alirezamogharabi8733]

@@SQLBI 🙏🌹

[@sessionel]

What if my fiscal year ends in June ? How I am going to proceed with my DAX query in order to capture Years and Quarters accordingly when I'm generating the Fiscal Years and Fiscal Quarters columns ? Thank you.

[@SQLBI]

There are many examples on DAX Patterns: www.daxpatterns.com/time-patterns/

[@jarrelldunson]

Question... when I use generate, the year is a number, so PowerBi automatically converts to a measure and summarized it. How can I convert it to a Dimension?

[@SQLBI]

You can use CONVERT: dax.guide/convert/

[@picious]

Documentation is needed either way.. just having clean code is not enough in my opinion. Having documentation (at least a process defined) in parallel is the best practice. Agree? Any tips for documentation?

[@SQLBI]

Yes, but the reality is that this doesn't happen all the times. The reality is that getting used to write code that speaks by itself is easier and more effective rather than inserting two lines of comment for every line of code. Documentation at the beginning of a block should describe the general approach (we are talking about code comments, not functional documentation here). Micro-documenting every line of code could be overwhelming - and the risk is that it's not maintained over time, leaving inconsistency between code and comments. Having both (good documentation AND good code with good naming) is the best of the two worlds, of course!

[@picious]

@@SQLBI thank your for your reply

[@xushuo1987]

Video around 7:36, should line 7 be ADDCOLUMNS instead of SELECTCOLUMNS?

[@SQLBI]

SELECTCOLUMNS is right, it is part of the explanation about how to customize the column names (see line 5).

[@xushuo1987]

@@SQLBI I see. I didn't see Line 9 which selected the [Date] column again. I thought it's an ADDCOLUMN behavior which adds bunch of columns like Year Month information to the original one-column table variable RenamedCalendar. Is there a special reason for using SELECTCOLUMNS at Line7 instead of using ADDCOLUMNS and starting from adding "Year","Month" to that variable?

[@aleksanderstrommer7677]

Enjoying to the bone