DAX HANDBOOK7. VARIABLES

If you wish to follow along, you can find PBIX/Excel files at the bottom of the article.

Guide Topics (red is currently selected one)

Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

Experienced data analyst. Advanced in SQL, PowerApps and M language.

Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout your measure. Few facts about Variables:

• They are defined with a keyword VAR, followed with DAX code of the variable
• When we are finished with defining the variable, we need to write a keyword RETURN, to state the end of the variable and to return its result
• Variables can hold both table and scalar values
• Variables retain lineage when possible
• Variables are evaluated in the context they are defined in, not the context they are used in.

Defining variables

```SalesBelow 100000 € =
IF(
SUMX(
FactSales,
FactSales[OrderQuantity] * FactSales[UnitPrice]
) > 1000000,
"A",
SUMX(
FactSales,
FactSales[OrderQuantity] * FactSales[UnitPrice]
)
)

```
```SalesBelow 100000 € Variables =
VAR HighSales =
SUMX(
FactSales,
FactSales[OrderQuantity] * FactSales[UnitPrice]
)
RETURN
IF(
HighSales > 1000000,
"A",
HighSales
)

```

Both calculations in the visual above return the same value. But the second one, using variables is much more readable. Now let’s focus on the calculation using variables. We first defined a variable called HighSales with VAR and RETURN syntax. Later we used that variable to check for if condition.

Variables returning scalars

This is an important topic to understand. Variables are always evaluated in the context in which they are defined. After they are evaluated, their result will stay fixed when used in other parts of the measure. This is particularly important to understand when using CALCULATE to modify filter context.
When you define a variable in the original filter context, if you use it in a modified one, the modifications to the original filter context do not apply to that variable!
We will explain this with the following example:

```Customers Selling More Than Average =
VAR AverageSales =
AVERAGEX(
VALUES( DimCustomer[CustomerName] ),
[SalesAmount]
)
RETURN
COUNTROWS(
FILTER(
VALUES( DimCustomer[CustomerName] ),
[SalesAmount] > AverageSales
)
)

```
```TotalNumberOfCustomers = COUNTROWS(VALUES(DimCustomer))
```

We want to check how many customers sell more than the average of the selected segment. We will focus on the first row, where the original filter context is DimCustomer[City] =”Toronto” and DimProduct[Color]=”Black”. [Customers Selling More Than Average] starts by defining a variable in which it stores the average sales among all customers. We can represent the AVERAGEX function in a DAX studio with ADDCOLUMNS function over VALUES table.

After the average has been stored in a variable, the rest of the code is executed. FILTER takes the list of all CustomerNames and iterates over it. For each row of that table, it checks if the [SalesAmount] of the corresponding customer is above the value of the variable. The value of the variable remains the same for each row of the iteration, since it’s evaluated in the moment of defining, which was outside of the iteration.
FILTER function will iterate over the following “virtual” table representation, eliminating each row where [SalesAmount] is not larger than [AverageSales].

Variables returning tables

Variables can also store tables, which can later be used to iterate upon or modify the original filter context. Let’s say we want to show the sales of only the top 3 most expensive products. We will focus on the second row in a table with the original filter context DimCustomer[City] =”Albany”.

```MaxPrice = MAX(FactSales[UnitPrice])
```
```SalesTop3 most expensiveproducts =
VAR top3products =
TOPN(
3,
VALUES( DimProduct[ProductName] ),
[MaxPrice], DESC
)
RETURN
CALCULATE(
[SalesAmount],
top3products
)

```

We will use the DAX studio to show what the variable top3products look like.

As we can see, when used under Albany city filter context, this variable returns a table with the top 3 most expensive products. We can now use this table in a CALCULATE part of the measure to modify the original filter context so that it now includes the following conditions:

1. DimCustomer[City] = “Albany”
2. DimProduct[ProductName] in { Touring-2000 Blue, 60
Touring-1000 Blue, 46
Touring-1000 Yellow, 46}

Important to notice is the fact that 2nd filter condition will be always formed based on the original filter context. For each City, the Top3 products will defer and will be formed under the City condition. Only after the city creates the original filter context, will the CALCULATE further limit its scope with top3 most expensive products in that city.

If we were to change the filter in a visual from DimCustomer[City] to any other, like DimProduct[CategoryName], the measure will still return the correct figure (sales of the top 3 most expensive products), but now under a different set of original filters.

Why did we emphasize the latter? To show you that in every measure you create there will be an interference between filters and the measure. It’s crucial to always have that in mind when creating measure. We’ve seen many DAX beginners trying to work against the filter context (limiting it or ignoring it altogether), while pushing all the logic into a measure. Every time you develop a complex measure, you first need to understand where it will be used, and how can you benefit from the help of the filter context. Work in conjunction with it, not against it. Your measures will be faster, easier to debug and more flexible.

To understand which part of the logic should be pushed to the measure, and which part should be left to filter context is a sort of an art and you will become better in it with practice, but we wanted to raise awareness from the start of your DAX journey.

Nested row context

Nested row contexts appear when we introduce an iteration when already operating in a row context. The easiest way to explain this feature is within a calculated column. We will demonstrate nested row context with a calculated column in which we want to “Sort” products based on their StandardCost. Sorting is always difficult to implement in DAX since it cannot directly access other rows of the table when in row context or other points in a visual when in filter context to check if there are any with higher/lower value. Therefore, we need to use a different technique involving nested filtering.

```RankCostDistinct =
VAR OuterCost = DimProduct[StandardCost]
RETURN
COUNTROWS(
FILTER(
VALUES( DimProduct[StandardCost] ),
DimProduct[StandardCost] > OuterCost
)
) + 1

```

We operate in a row context of a calculated column, meaning there is no filter context coming from visuals. The same calculation will be evaluated for each row in a DimProduct table. We will explain what is happening in the third row of a table (the one in a red square). First, we will store a scalar value of the StandardCost in a variable called OuterCost. For the selected row that value equals 2077.27. After the value has been stored in a variable, we start with the rest of the calculation. COUNTROWS will count the rows of a table that survive the FILTER condition. FILTER function is the most important to understand. FILTER is an iterative table function, which creates an iteration over the table supplied as a first argument. We used VALUES function to form a table of unique StandardCost. We can represent this table with a DAX studio.

Remember, we are operating in the 3rd row of a calculated column. Can the VALUES function see all values from the list of unique StandardCosts? It can because there is no filter context limiting the VALUES function, so for every row in a calculated column, the VALUES( DimProduct[StandardCost] ) will return the same table with all the costs.

The FILTER function creates a nested row context, in which it iterated over the result of a VALUES function. For each row of the nested iteration over the result of a VALUES function, FILTER checks if the DimProduct[StandardCost] (the one bound to the inner iteration) is larger than the value stored in OuterCost variable. Notice that we are comparing the values from the same column, but since the variable value was defined in a context of an outer iteration, its result can be used inside the inner VALUES iteration. Condition of the FILTER function in the selected row looks like this.

Only 2 rows in a VALUES table survive the condition. COUNTROWS aggregates those rows in a scalar, and we add 1 to the result (1 is added so that the index doesn’t start from 0). The same calculation is performed for every row in the column. The variable defines which value will be used to test conditions upon. If we weren’t to use a variable, the calculation would not work because we would compare the same inner values twice. Here it’s also important to understand that variables are evaluated in the context they are defined, not in the one they are used. If the latter would be true, then the condition would show the following result:

For each nested table, both standard and outer cost would come from the nested iteration, meaning no rows would survive the condition.

Materials

Show All DAX Guide Topics (click to expand)

We wish to create the best possible content!

If you are a novice looking for a better explanation of any element of the topic, feel free to comment on the part you didn't quite understand!

If you are an expert in DAX and believe certain topic lacks important internals, your comments are more than welcomed!

Subscribe
Notify of
Inline Feedbacks

OUR SERVICES

Prefer live training or consultations?

DAX HANDBOOK7. VARIABLES

Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout...

DAX HANDBOOK6.8 ALLSELECTED

Explanation ALLSELECTED is one of the most complex functions in DAX. When used improperly it can lead to unexpected results. As a rule of thumb, you should not use it in iterative functions. It is preferable to use it only as a CALCULATE filter remover, not as a table function....

DAX HANDBOOK6.7 Lineage

What is Lineage? Lineage is a part of DAX mechanics that enables us to use tables as filter arguments for the CALCULATE function. It’s also used in row2filter context transition and other aspects of the data model that involve filter propagation throughout the model. We can state that lineage is...