It’s pretty early in the morning here in Sydney Australia (just after 6 am actually). I just finished a fun YouTube session with Avi Singh where I demonstrated my approach to using variables in DAX to help write more complex formulas. Avi streamed the session live on YouTube, and it is also there available to watch later (see below). A few of the viewers asked for a copy of the workbook I used – you can download that file here.
Key Points on Why I Use Variables in DAX
- I can break a big problem into smaller pieces, solving each piece of the puzzle one at a time
- For each piece of the puzzle, I get to test the result and check it is correct before moving on.
- The names used to define the variables are descriptive, and hence they form part of the documentation and explanation of how the formula works.
- The measure fully encapsulates the entire logic of the calculation. This can make it easier to debug compared to writing interim measures.
- They can be more efficient to execute.
Video
Here is the YouTube video where I show how to write the following custom time intelligence measures using variables.
- Rolling 90 day sales
- Avg daily sales over last 90 days
- User selectable time horizon for rolling daily sales history (great idea Avi).
Let me know what you think about variables, and any tricks you have on how you use them in your DAX formulas.
Matt, just watched video from roughly 2 years ago & followed to this blog. I am still getting my head into variables and think this might be a good area to expand in the next edition of both your book and the course training videos. Thanks.
Glad I looked at your pbix file. I was curious to see how you deployed the slicer. I discovered GenerateSeries which is nice to know.
How do you use the TimeHorizon measure?
TimeHorizon Value = SELECTEDVALUE(TimeHorizon[TimeHorizon])
From memory, it is used as an input to one of the other measures
Thanks, Matt. These 2 days I kept busy myself learning all the things (End-to-End) you taught here. I enjoyed much, learned much and I am sure, my base in the Power BI Journey is much stronger now. Thanks also for the PBIX and the source excel file to go through each step for a complete understanding.
Rgds..Aziz
Hi Matt,
In the video 22-23 minutes, you did the sort month – how you did that? Appreciate your help. Thanks.
Rgds..Aziz
I was able to do it by sorting “MonthNumberOfYear”. However, after I take out the field “MonthNumberOfYear” from the report, the sorting goes back by name of the month which I do not want. Thanks.
There is a button called “sort columns”. I don’t recall which menu, many the modelling menu. Select the month name, then click sort columns and select month number of year
Thank you so much, Matt. Got it. Done.
Hi Matt, one more query – at abput 32 mniutes in yuour video, you drilled-down the Sept 2001 figure. I am not able to do that as drill down icon is “grayed out” – what tips you suggest. Thanks in advance for help.
Rgds..Aziz
Pls ignore my request – You moved date field on rows… Learning a lot from your video. Thanks.
Nice work Matt. I do agree with the learn and learn again comments. Having attended your 1 x day training course at Difinity in Auckland this year… I want to do it again next year. I need more stuff to “click” or sink in.
And I love that you super charged the example towards the end of the presentation… Excellent.
Unfortunately I was late submitting my pre-conference session for next year and I missed out. Here’s hoping one of the other sessions presenters will need to cancel 🙂
Hi Matt,
Great presentation today. I downloaded the PBIX file, but it looks like I need the “5 Tables in Excel” Excel file to work through the queries. Any chance of making that available? Thanks.
Hi Steve. There is nothing special in the queries, but I have added the source file to the zip file for you anyway.
Thanks. I did just occur to me that I didn’t need the data to look at how the measures work.