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.
Some times its a pain in the ass to read what people wrote but this website is real user genial! .
Can I just say what a reduction to search out somebody who actually knows what theyre speaking about on the internet. You definitely know the way to deliver a problem to light and make it important. Extra individuals must read this and perceive this aspect of the story. I cant believe youre no more popular because you positively have the gift.
Aw, this was a very nice post. In thought I would like to put in writing like this additionally – taking time and actual effort to make a very good article… but what can I say… I procrastinate alot and under no circumstances seem to get something done.
Excellent goods from you, man. I’ve be aware your stuff prior to and you are simply extremely wonderful. I really like what you’ve received right here, really like what you are saying and the way in which during which you are saying it. You are making it enjoyable and you still take care of to stay it sensible. I cant wait to learn much more from you. That is actually a terrific web site.
Thank you for your sharing. I am worried that I lack creative ideas. It is your article that makes me full of hope. Thank you. But, I have a question, can you help me?
I regard something really interesting about your website so I saved to favorites.
I like this website very much, Its a very nice situation to read and incur information. “Education is the best provision for old age.” by Aristotle.
Please let me know if you’re looking for a article author for your weblog. You have some really good posts and I believe I would be a good asset. If you ever want to take some of the load off, I’d love to write some articles for your blog in exchange for a link back to mine. Please send me an email if interested. Kudos!
Thank you for the good writeup. It in fact was a amusement account it. Look advanced to far added agreeable from you! By the way, how can we communicate?
Fantastic beat ! I would like to apprentice whilst you amend your website, how can i subscribe for a blog web site? The account helped me a applicable deal. I have been a little bit acquainted of this your broadcast offered shiny transparent concept
You are a very capable person!
I love it when people come together and share opinions, great blog, keep it up.
There are some attention-grabbing deadlines in this article but I don’t know if I see all of them middle to heart. There is some validity but I’ll take hold opinion until I look into it further. Good article , thanks and we wish extra! Added to FeedBurner as effectively
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.