Using Variables within DAX

Power of Variables in DAX

Every so often you find a hidden gem, something so valuable, just hiding there in plain sight.  Recently, I found out that I LOVE the using Variables with in a DAX expression.  Ok, brief introduction, I was trying to calculate sales changes between different years.  The data looked similar to the following table:

Year Month Sales
2013 1 20
2013 2 25
2014 1 22
2014 2 23
2015 1 21
2015 2 31

I wanted to sum the sales for each year and compare the percent change between each year.  If you have followed my blog for a while, I have a ton of tutorials about percent change.  ( See list of other tutorials dealing with percent change )

Now, for the twist making this calculation more difficult.  I want to calculate percent change even when a filter is applied by clicking on a visual.  By using variables we can apply some data modeling voodoo magic to get the desired results.  See a sample of the measures working in the Power BI Embedded sample below:

Let’s dive in!

Open up PowerBI Desktop, Click the Get Data button on the Home ribbon and select Blank Query.  Click Connect to open the Query Editor.  On the View ribbon click the Advanced Editor button.  While in the Advanced Editor paste the following code into the editor window, click Done to complete the data load.

Note: If you need some more help loading the data follow this tutorial about loading data using the Advanced Query Editor.  This tutorial teaches you how to copy and paste M code into the Advanced Editor.

let
 Source = Excel.Workbook(Web.Contents("https://powerbi.tips/wp-content/uploads/2017/05/Clothing-Sales.xlsx"), null, true),
 ClothingSales_Table = Source{[Item="ClothingSales",Kind="Table"]}[Data],
 #"Changed Type" = Table.TransformColumnTypes(ClothingSales_Table,{{"Date", type date}, {"Category", type text}, {"Sales", Int64.Type}}),
 #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), type number)
in
 #"Inserted Year"

Your loaded data should look like the following:

Load ClothingSales Data
Load ClothingSales Data

Rename the Query to ClothingSales, and click Close & Apply to load the data into the data model.

It is a good practice to review the data columns and verify that the formatting is set up correctly.  To do this click on the the column Date in the Fields pane, then click on the Modeling ribbon.  In the Formatting section change the format to Date Time and select M/d/yyyy format.  Repeat this for the following columns:

  • Format Sales to Currency then select $ English (United States)
  • Format Year to Whole Number

With our data properly formatted we are ready to build a simple bar chart.  See the image below for the bar chart set up:

Create Bar Chart
Create Bar Chart

To start we will create a explicit calculation for the total selected sales.  On the Home ribbon click the New Measure button and enter the following code:

Total Selected Sales = SUM(ClothingSales[Sales])

Pro Tip: To learn more about explicit vs implicit calculations within Power BI Click the following link.

Add a Card visual and add our new measure.

Add Card Visual
Add Card Visual

Let’s change the formatting measure so we can see the full number.  With the Card still selected click the Paint Roller expand the section labeled Data label and change the units from Auto to None.  This allows us to see the entire sales number.

Remove Number Formatting from Card
Remove Number Formatting from Card

Let’s enhance this card visual further by adding dollars formatting.  Make sure you have the Total Selected Sales measure selected by Clicking on the words of the measure.  Then, click on the Modeling ribbon click and change the Format to Currency, then select $ English (United States). The final format of the card visual should look like the image below:

Final Card Format
Final Card Format

For the next step we will repeat the previous steps for our new measures.

  1. Create the measure
  2. Change the formatting of the card
  3. Change the formatting of the measure

The next measure will calculate the prior year total sales, but only calculate this value when a filter context from a different visual is applied.  Take note this is the magic I was talking about!!

Prior Year Sales = 
VAR 
selectedYear = if( HASONEVALUE(ClothingSales[Year]), 
    FIRSTNONBLANK(ClothingSales[Year],ClothingSales[Year]),
    BLANK() )-1
Return 
    CALCULATE(
        SUM(ClothingSales[Sales]),
        ALL(ClothingSales),
        ClothingSales[Year] = selectedYear)

I am going to pause a second here and explain what is going on in this measure as we have multiple things going on.  First, we are creating a variable with the VAR.  In the below image I show you that the variable that we are defining is called selectedYear.  SelectedYear returns a value of blank if multiple years are selected.  This is accomplished by using the if statement and the HASONEVALUE() DAX formula.  The Return in then allows us to output a value.  In this case we are using the CALCULATE() to sum all the sales.  The next part is crucial to making this work.  Within the Calculate we are applying two filters.  The first filter selects the entire table with the All() then we apply the filter from the earlier defined variable with the name of selectedYear.

Prior Year Sales in Color
Prior Year Sales in Color

Apply the same formatting steps 2, and 3 mentioned earlier.  Now, we can select one of the years within our bar chart.  Doing so populates the two measures we created.  The selected year total sales, and the prior year of sales. I selected the year of 2014, which has sales of $11k.  Hover your mouse over the bar for year 2013 and the tool tip will appear showing a total sales of $10K.

Selected Bar
Selected Bar

Now we will make our percent change measure.  Enter the following as a new measure:

Percent Change = DIVIDE([Total Selected Sales],[Prior Year Sales],1)-1

Pro Tip: Use the DAX function DIVIDE when dividing two numbers, this protects against odd cases where the denominator is zero.

Click on the Modeling ribbon and change the formatting of the measure to Percentage.  Add a final measure that calculates the difference.  See measure below:

Prior Year Delta = [Total Selected Sales] - if( [Prior Year Sales] = BLANK(), [Total Selected Sales],[Prior Year Sales])

This measure calculates the total changes in sales between the two selected years.  The if statement at the end of the equation handles a special case that if you don’t have any individual year selected the prior year delta defaults to zero.

Thanks for following along an learning about variables.  Trust me, these are super helpful.  Make sure you spend some learning how to leverage variables in your DAX equations.

This Tutorial was inspired by the following article I found from SQLBI authored by the master of DAX Alberto Ferrari.  I mean, come on, Ferrari, how baller is that last name!  I guess that is why he is the master.  Kudos to you Alberto!

Still need more reading about DAX, check out the following books:

25 Comments

  1. Hi Mike – I really want to try this, and the instructions look good, however, it is the wrong M code to load the data. It is for some Crime data instead. 🙁

    • Give it a retry, I have fixed the M code. Sorry for the mistake.

  2. Hi,

    there seems to be a mismatch between the query you psted (is about “US Crime Data_Sheet” and the data you worked with (sales data).

    Best Regards,
    Stephan

    • There was, sorry, I made a boo boo on the M code. It should be fixed now.

  3. It would be nice to do this tutorial if the data set reference were correct. I’m surprised that no one else has inquired as to why it’s US crime data yet the steps clearly refer to clothing sales?

  4. The PowerQuery M query refers to the wrong dataset I think.
    US-Crime-Data.xlsx

  5. Hi Mike,
    If I use the M code provided, I got US Crime Data instead of Clothing Sales..
    Do you have the correct code, or am I missing something? Thanks!

    • You are not missing anything, I actually made a mistake, sorry. I think I have the correct M code in the tutorial now.

  6. Hi Mike, I just found you on the web. Love reading your subjects and its contents. I am particularly interested in “the power of using variables”. However I can’t find the right data file since you refer to USA Crime(“http://www.powerbi.tips/wp-content/uploads/2017/02/US-Crime-Data.xlsx)

    Can you help me out?

    • I botched the tutorial and loaded the wrong M code into the tutorial. I have since fixed it, give it a whirl again it should be correct now.

  7. Hi there! This is the just the thing I was looking for!

    I just noticed that your query is pointing to “US-Crime-Data.xlsx” instead of clothing sales, you may want to fix that.

    Thanks for this post!

  8. The query is pulling crime statistics not clothing sales. But I got the general direction this was going. Useful.

    • You are correct, my bad, I botched it. I have updated the tutorial to use the correct M code. It should make waaaay more sense now. So sorry 🙁

  9. Am I missing something – the code embedded to cut and paste refers to Crime but the post is dealing with clothing sales.

    • You indeed are correct, I botched this one, I copied the wrong M code into the post. I have updated the post to be correct. Sorry, for the mistake. It should make more sense now.

  10. Hello Mike,
    Ton learn more about DAX and the variables, I would like to follow your tutorial by trying it out myuself. My question therefore is where you got the xlsx from?
    Greetings,
    Kim

  11. Hi Michael,

    I have an error and I wonder if you or someone can be so kind as to help me solve it. It goes like this:

    “Error Message:
    MdxScript(Model) (13, 9) Calculation error in measure ‘Member'[Prior_Day_Acquisition]: DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.”

    I know it has to do with the “prior year sales” calculation, that in my case is “user acquisition” through time…

    Thanks in advance!
    A

    • Try checking the data types within the Query Editor. It looks to me from that error that you might have a mixed up data type. This means a column that you expect to be a date format is actually formatted as a text field. Sometimes this happens when you are bring in columns of data from an external source and Power BI does not automatically recognize that the column type should be a date and not just text.

  12. Thank Mike, very usefull. Why use FIRSTNONBLANK? You already know there is one value. Just VALUE() would be sufficient.

    • That is a really good point. To be frankly honest I didn’t understand Value at the time of this blog post. I like your suggestion to the solution, it is elegant.

  13. Good way of telling, and good piece of writing to get information about my
    presentation focus, which i am going to convey in university.

  14. I’m having issues with my data model regarding dates for the Prior Year measure (or Prior Month for me). I have a column for Year, Month (text) and Period, which is dddd, d MMMM yyyy. I understand that my Month is text, which I cannot use for this formula and my Period column is linked with my Date Table.

    So I wish to show Month to Month, instead of Year to Year, the way you showed. I put it in the Period in replacing your Year, but nothing shows up. However, the coding appears to be fine. Would you be able to assist me with this?

    Prior Month Alert =
    VAR selectedPeriod =
    IF (
    HASONEVALUE ( ‘TRG Alert Data'[Period] ),
    FIRSTNONBLANK ( ‘TRG Alert Data'[Period], ‘TRG Alert Data'[Period] ),
    BLANK ()
    ) – 1 RETURN
    CALCULATE (
    SUM (‘TRG Alert Data'[Total Alerts]),
    ALL ( ‘TRG Alert Data’ ),
    ‘TRG Alert Data'[Period] = selectedPeriod
    )

1 Trackback / Pingback

  1. #Excel Super Links #32 – shared by David Hager | Excel For You

Comments are closed.