Excel 2016: DAX Variables

 business-925900_640

DAX Variables

Well, DAX variables weren’t actually introduced with Excel 2016; they weren’t even introduced in 2016. Come to think of it, DAX variables aren’t even variables. Nevertheless, DAX variables are one of the most interesting and exciting recent additions to DAX. DAX variables were introduced to the Power Pivot engine in 2015, so the same improvements in DAX now affect Power BI, Excel Power Pivot, and the SQL Server Analysis Services tabular mode. Excel 2016 is, therefore, the first Excel version to possess DAX variables. Called “variables” for lack of a better name, DAX variables are actually named and structured expressions. Once an expression is given a name, it can be invoked anywhere within the larger expression in which it was defined.

DAX variables do not allow you to calculate anything that could not have been calculated previously. Their primary value is clarity, but it is worth noting that the advantages of clarity can extend to the query optimization process, and DAX variables may result in superior query plans.

The example queries in this blog target the ContosoRetailDW sample data and may be downloaded here. The sample file has the extension “mdx” simply to ensure that if opened in the Management Studio the keywords will be properly colorized.

Here we have an example of a very simple usage of a DAX variable:

DEFINE

MEASURE ResellerSales[Rating] =

VAR amt = SUM(ResellerSales[SalesAmount])

RETURN

SWITCH(

TRUE()

,amt > 1000000,“A”

,amt > 100000, “B”

,“C”

)

EVALUATE(

SUMMARIZE(

ResellerSales

, Product[ProductName]

,“Sales”,SUM(ResellerSales[SalesAmount])

,“Rating”,[Rating]

)

)

DAX variables require the “var” keyword, which attaches a name to a column value or measure, and a “return” section that uses the variable in a calculation and decides what value is to be returned.

The value returned can be a numeric or string value as shown here, or a table value. Columns in table-valued variables cannot be referenced using the TableName[ColumnName] syntax used for actual tables

Variables can be used only within the scope with which they have been declared. Variables can refer to previously defined variables, but only within the same scope.

This query will fail, because “amt” is not defined for the second measure, i.e. is “out of scope”.

DEFINE

MEASURE ResellerSales[RatingAlphabetic] =

VAR amt = SUM(ResellerSales[SalesAmount])

RETURN

SWITCH(

TRUE()

,amt > 1000000,“A”

,amt > 100000, “B”

,“C”

)

MEASURE ResellerSales[RatingNumeric] =

VAR Just_A_Test = SUM(ResellerSales[SalesAmount])

RETURN

SWITCH(

TRUE()

,amt > 1000000,1

,amt > 100000, 2

,3

)

EVALUATE(

SUMMARIZE(

ResellerSales

, Product[ProductName]

,“Sales”,SUM(ResellerSales[SalesAmount])

,“Rating”,[RatingNumeric]

)

)

Dax evaluates variables in the context of their definition, not the context in which they are used. We must be aware of this to obtain the results we desire, but we can also take advantage of this.

The following query returns products from the “computers” category that constitute more than 0.2% of Contoso’s total sales.

DEFINE MEASURE ‘ResellerSales’[Total Sales Amount] = SUM([SalesAmount])

EVALUATE

CALCULATETABLE (

ADDCOLUMNS (

FILTER (

VALUES ( Product[ProductName] ),

‘ResellerSales’[Total Sales Amount]

>= CALCULATE (‘ResellerSales’[Total Sales Amount] , ALL ( Product ) ) * 0.002

),

“SalesOfProduct”, ‘ResellerSales’[Total Sales Amount]

),

ProductCategory[ProductCategoryName]=“Computers”

)

But what if we wish to view computer products that constitute more than, say, 1.0% of the total sales for computer products?

DEFINE MEASURE ResellerSales[Total Sales Amount] = SUM([SalesAmount])

EVALUATE

CALCULATETABLE (

ADDCOLUMNS (

VAR

PercentOfSales = ResellerSales[Total Sales Amount] * 0.01

RETURN

FILTER (

VALUES ( Product[ProductName] ),

ResellerSales[Total Sales Amount] >= PercentOfSales

),

“SalesOfProduct”, ResellerSales[Total Sales Amount]

),

ProductCategory[ProductCategoryName]=“Computers”

) ORDER BY ResellerSales[Total Sales Amount] DESC

In the second query, the definition of the variable is evaluated within the context of product =”computers.” Once again, this query could have been constructed without using DAX variables, but the variable implementation is clear and easy to appreciate.

Conclusion

DAX variables, introduced in 2015 in the Power BI engine, are now available in Excel 2016 and the Analysis Services tabular mode for SQL Server 2016. The use of DAX variables can make complex DAX expressions more readable and have the potential for improving performance as well.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.