Table and column references using DAX variables

Using variables in DAX makes the code much easier to write and read. You can split a complex operation into smaller steps by storing a number, a string, or a table into a variable. When you store a scalar value in a variable, the behavior is intuitive and common to many other languages.

For example, you can write a measure computing the margin percentage this way:

The variables Revenues, Cost, Margin and MarginPerc contain numbers that are used in subsequent DAX expressions after each variable definition. Every value is read by simply referencing the variable name.

A variable can also store a table, which can be used as a filter argument in CALCULATE. For example, the following measure computes the sales amount of the top 10 products in any given selection of the report – such as the top 10 products of a color or of a category, depending on the report selection:

The Top10Products variable is like a temporary table that contains all the columns of the Product table. The entire result of TOPN is used as an argument of the following CALCULATE, so we do not have to think about how each column of the table in Top10Products could be accessible.

But what if we want to create a measure that lists the top three products of the current selection? Without using variables, the measure can be as follows:

You may notice that the Sales Amount measure is evaluated twice for the top three products found. Indeed, the Sales Amount value computed in TOPN is not persisted in the result of TOPN, which only contains columns of the Product table. We will see how to optimize this later. For now, just focus on how CONCATENATEX uses the result provided by TOPN: the Product Name column reference uses Product as a table name. This seems intuitive because TOPN returns a result which is just a filtered set of rows of the Product table. However, what happens if we assign the result of TOPN to a variable? The code is not much different:

However, a developer might make the wrong assumption that assigning a table to a variable transforms the variable into a table, with its own columns and a new set of column references. This is not the case. Indeed, you cannot write the following code:

This code generates the DAX error, “Cannot find table Top3Products”. A column reference must always reference an existing column of the data model, or a column that has been generated using a table function assigning a specific name to it.

Thus, a variable name cannot be used as a table name in a column reference. However, what happens with new columns created within a DAX expression? We can see a useful example trying to avoid the double calculation of Sales Amount by the CONCATENATEX function, which needs to compute Sales Amount to establish the display order of the products:

The ProductsSales variable contains a table with all the columns of Product, plus an additional column (Sales) with the result of the Sales Amount measure computed for each product. This way, the syntax [Sales] is just a column reference, not a measure reference. Indeed, there is no measure named Sales in the model. Once again, the following syntax would be invalid, because ProductsSales is a variable and not a table:

However, there are two options if you want to use an explicit column reference to make the code easier to read. These two options fully respect the following two important rules for DAX code formatting:

Always use table names for column references

Never use table names for measures

The first option is to use the empty table name in the column reference. The following measure is valid:

The current version of Power BI Desktop (April 2019) marks the two column references “[Sales] as an IntelliSense error, but this is a valid DAX syntax and the measure works without any issue. Yes, this is a bug in IntelliSense!

The second technique that can be used to fully respect the best practice for column references is to name the column including a table name in the ADDCOLUMNS function. You can use either existing names or new names, including the name of a variable!

Is it necessary to use one of these techniques? Usually, when the new column name is unique and the DAX expression is simple enough, we can live with an exception to the best practice for column references. But in case you have a complex model and a complex measure, you may consider using the latter technique – also making it clear that the table name is that of a variable using one technique described in the Naming Variables in DAX blog post, such as a double underscore prefix for variable names:

Using the variable name as a table name for new columns created by ADDCOLUMNS, SELECTCOLUMNS or other similar DAX functions can be a good idea to make the code simpler to read in a very long and complex DAX expression. We do not however think that is necessary in simple measures like the ones described in this article!