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.
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.