Sign in or start a free trial to avail of this feature.
4. Working with Variables
Introduced in 2015, variables allow you to make long DAX formulas much easier to write and read. We’ll see how to create variables in this lesson.
- A variable calculates andstores the value of an expression outside the main body of a formula
- Variables are created before the main formula, and then referred to within the formula using just their name
- Using variables makes it easier to write long formulas or formulas which use a particular expression several times
- By writing the code for a variable once only, you also reduce the risk of errors
- Variables are defined using the VAR keyword
- You can define multiple variables in a function, placing VAR before each one
- After defining variables, you type RETURN before the main body of the formula
- F4 – Enter formula bar
- Alt H, PT, T – Create new PivotTable (from Power Pivot window)
- Alt H, S, S – Sort data in ascending order
When writing DAX formulas, you may have certain values or expressions that feature frequently.
Repeatedly typing out the same text not only makes your code more difficult to read, but also makes it more likely that you'll make a mistake somewhere along the line.
To address this, DAX introduced variables in Excel 2016. Variables are defined outside the main body of the formula and can then be referenced within the formula.
This means you write the code needed to create the variable only once.
In this lesson, we'll look at how to use variables.
We'll do this by creating a product category column, which categorizes each product as small, medium or large, according to it's total sales.
We created a similar column in the previous course. But here, we'll see how variables can make the process easier.
Off-camera, I've added a new product details table to the model and created a relationship between this table and the Pharma Data table.
The product details table currently has just one column listing the name of each product.
We'll add the product category column to this table.
In the previous course, we created a column calculating total sales for each product and then another column to determine the category based on those sales.
While this method worked, it involved creating two calculated columns.
This is generally not best practice, as each column takes up space in our data model.
As there are only 20 rows in this particular table, the effect is not huge. But adding columns to larger data tables can have a noticeable impact on performance.
In this case, we'll use variables to create the category column, without the need for the initial sales column.
We'll add a new column to the product details table and call it product category.
We'll define the variable with the word VAR in the formula bar. We'll call the variable TotalSales and set it equal to a calculate formula.
The first argument of the calculate is the sum of sales.
The second argument is the related table function applied to the Pharma Data table.
In the previous course, we used the related table function to create a calculated column showing the sales for each individual product.
In this case, we are using the same formula in a variable instead.
We could define multiple variables by putting VAR before each one, but in this example, we only need the one.
When we've defined the variable, we then add RETURN to indicate that we're now defining the output of the formula.
Our product category column, we use a simple IF statement along the lines of the one we used in the previous course.
As we've calculated the total sales in our variable, we can use the variable name when creating our IF condition.
Products with sales of less than 20,000 are classified as small.
If total sales are larger, they'll go through another IF statement.
Sales of less than 50,000 are classified as medium, while sales above 50,000 are classified as large.
Note that this condition can be read clearly and does not involve creating an extra calculated column as we did in the previous course.
Let's press Enter to create the column.
To confirm this formula is working correctly, let's quickly create a new pivot table and put it on a new sheet.
We'll expand the product details table on the right, add product name to rows, product category to columns, expand the Pharma Data table, and add sales to values.
We'll right-click any value in the grand total column and sort from smallest to largest.
It's now clear that the products are correctly classified according to their total sales.
In this lesson, we've introduced the concept of variables and seen a simple example of how they work.
One advantage to variables is that they can make long code easier to read and write. Because variables are calculated outside the main formula, they can be used when a DAX formula has multiple contexts.
This is a concept we'll cover later in this course.
In the next lesson, we'll learn about relational functions, which allow you to refer to columns from different tables in your DAX formulas.