Using Variables to Write Measures - Excelerator BI

Using Variables to Write Measures

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

  1. I can break a big problem into smaller pieces, solving each piece of the puzzle one at a time
  2. For each piece of the puzzle, I get to test the result and check it is correct before moving on.
  3. The names used to define the variables are descriptive, and hence they form part of the documentation and explanation of how the formula works.
  4. The measure fully encapsulates the entire logic of the calculation. This can make it easier to debug compared to writing interim measures.
  5. They can be more efficient to execute.

Power BI Online Training

Video

Here is the YouTube video where I show how to write the following custom time intelligence measures using variables.

  1. Rolling 90 day sales
  2. Avg daily sales over last 90 days
  3. 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.

15 thoughts on “Using Variables to Write Measures”

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

  2. 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])

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

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

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

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

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

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top