Improve DAX queries by using GENERATE and ROW instead of ADDCOLUMNS when you create table expressions. Article and video: sql.bi/54337?a... How to learn DAX: www.sqlbi.com/... The definitive guide to DAX: www.sqlbi.com/...
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?
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.
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).
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.
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.
@@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?
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.
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?
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.
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?
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!
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?
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.
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?
