Using Variables In Power BI – A Detailed Example

In this post, I’ll show you how to use and declare variables within your DAX formula in Power BI. You may watch the full video of this tutorial at the bottom of this blog.

Variables are a real game-changer for the formula bar in Power BI. Being able to break down your more complex formulas in steps makes setting them out much more intuitive.

I keep thinking back to using VBA with these variables. They make your life in VBA about ten times easier – and the same can be said for using them within Power BI.

Before diving into how to actually create these variables, let us first talk about the advantages as well as the different nuances to using variables.

Table of Contents

Advantages And Nuances To Using Variables In Power BI

Variables within the formula bar are very flexible. They can take in other expressions (or measures) as well as table functions, including filters.

When you use filters a lot, these can take up a bit of room and your formulas can get messy, so being able to place these table functions in a variable is a great idea.

However, there are a few nuances to using them.

The big one to get your head around is the context considerations of placing either calcs or other expressions inside a variable.

Really understanding this well takes a bit of trial and error, so make sure to look into it.

How To Use Variables In Power BI

Let us now proceed to actually incorporating these variables in our DAX formulas.

1. Creating A Simple Measure And Adding The Variable

We currently have a table in our canvas that shows the total sales per customer.

Now, what we want to do is to look at the total export sales.

original table, power bi variables screenshot 1

So we’re going to create a simple measure and call this total export sales.

Now, instead of going straight to writing your calculation, we are going to write VAR which stands for variable.

After this, we’re going to jump down one row and then create the actual variable.

Let’s call this ExportSales and then create a table. What we’re doing here is capturing the table using the ExportSales variable.

adding variables in power bi, variables in power bi screenshot 1

So, the table should look into all my sales, but show only the ones that I export.

adding filter in the variables in power bi

2. Writing The Actual Calculation

The next step now is to create the actual calculation. We’ll jump down one row again and then go RETURN. After this, we’re going to add CALCULATE.

Inside the CALCULATE statement, we’ll go Total Sales. Originally, we have to add a filter after the total sales. But since we already created the ExportSales variable, there’s no need for us to write the filter. We’re just going to add the variable.

Notice how easy it is to read the formula now compared to if we’re going to include the whole filter inside CALCULATE.

Now we have our Total Export Sales.

If we push enter and drag this measure into our table, we’re going to have the total export sales per customer column.

table with total export sales column after using variables in power bi, power bi variable screenshot 1

So you have seen how to use a simple variable in Power BI.

What you need to remember at this point is that, in creating the Total Export Sales measure above, you could have chosen to just create a simple measure, and add CALCULATE with the whole FILTER statement.

However, the ExportSales variable enabled us to simplify our code.

Perhaps you’ll be able to appreciate this better when we have a more complex formula with multiple variables.

So let’s add some more variables to our Total Export Sales measure.

Creating Complex Variables In Power BI

Looking back to our formula, we have a Total Export Sales measure which includes the ExportSales variable and then calculates the export sales using the variable we created instead of the whole filter statement.

reviewing the total export sales measure using variables in power bi, var in power bi screenshot 1

What we’re going to do now is to add the Days variable.

So, below the ExportSales variable, we’re going to write VAR and then below this is the number of days.

Of course, you can write whatever number you want here. But for this example, let’s write Days=365.

And then, we can utilize that scalar value, or the 365 days, inside the CALCULATE statement that we originally created.

So we’re going to filter the date so that if it’s less than TODAY and Date column is greater than or equal to TODAY minus the Days variable.

Notice here that we have moved the ExportSales variable inside the CALCULATE one row below and the same for the filter statement so that the formula is easier to analyze.

Now that we have the Days variable, the Total Export Sales column in our table now only shows the export sales within 365 days.

total export sales with days variable filter

We can make this formula even more complex and add the IncludedDates variable.

Then we can transfer the filter statement to the IncludedDates variable.

transfer filter to included dates variable

We can now replace that filter statement with IncludedDates so that the formula is easier to look at from a syntax perspective.

replace filter statement with included dates to utilize variables in power bi

What is more interesting is that we can add comments below our entire formula.

So all these variables plus the comment that we have added are going to make it so much easier for you to understand the formula at a later point.

Aside from that, should any of your colleagues need to work on it, it would be easier for them to break down and understand what is going on.

Conclusion

We have just worked on how to use variables in Power BI. We started by creating a simple measure and then instead of immediately proceeding to the calculation, we added a variable and then later on added multiple variables to our formula.

We have seen how these variables have extremely simplified our formula which can really benefit us or anyone who will use the formula at a later point given that it is now so much easier to understand.