Power BI: How To Use Variables In DAX Formulas

Using variables in Power BI can help simplify your report-building process while making the dashboard more efficient. In Power BI, you don’t need to specify a data type for the variable as it is automatically assigned.

You can declare a variable using the VAR kyeword, give it a name and an expression for its value. You must also use a RETURN clause to return the final value of the entire expression

How to Declare a Variable in DAX

The syntax for declaring a variable in DAX is:

VAR <name> = <expression> where

• <name> is user defined as is the variable name that can be used throughout the code.
• <expression> can be either a value or a DAX expression. It returns either a scalar or table as the result.

Here are some examples of expressions that can be used as the expression:

There must a RETURN statement to complete DAX code that uses variables.

What characters are allowed in the variable name?

The variable name rules are very similar to other programming languages such as Python. Here’s the list:

1. a-z, A-Z, 0-9 characters are allowed (Exception: 0-9 are not allowed as first character)
2. __ (double underscore) is allowed as a prefix

What characters are NOT allowed in the variable name?

There are some rules to remember when naming your variables since there are things you can’t use in your variable name. Knowing them ahead of time will save you a headache. Things that are not allowed in your variable name are:

1. Special characters are not allowed (other than underscore)
2. Reserved keywords are not allowed
3. Names of existing tables are not allowed
4. Empty Spaces are not allowed
5. Delimiters are not allowed (commas (,), semicolon (;), quotes(‘, “), braces({}), pipes (|), slashes (/\)

Benefits of Using Variables in Your Power BI Dashboard

How Does Using Variables Improve Performance?

A variable is only evaluated once but can be used multiple times inside the DAX query. So using a variable for parts of the code that is repeated many times improves performance since the variable doesn’t need to be evaluated every time it is used in the code.

How Do Variables Improve Readability?

Instead of creating multiple measures, you can get the same result by creating multiple variables in the same DAX query making your code much more readable that can be easily edited. This also improves performance because the result of the expression is stored in the variable and doesn’t have to be recalculated each time it is used.

Where Can We Use Variables?

You can use variables wherever you can use DAX such as:

1. Calculated Columns
2. Calculated Measures
3. Calculated Tables
4. Nested Variables

What Can We Include in the Variable Expression?

ere are some other things to remember when using variables in your DAX query:

• Variables can reference measures
• Variables can reference other variables (that were defined before it)
• For variables that create a table, you cannot reference its columns like you can normal tables (Eg TableName[ColumnName])

Scalar Values

Here we’ve created a variable for the number of stores called num_stores and assigned the scalar value of 215 to it.

VAR num_stores = 215

Measures

If you’ve created a measure like this:

Sum of Sales = sum[Sales]

You can now use the measure in the variable like this:

VAR totalSales = [Sum of Sales]

Tables

You can use a variable to store a calculated table which can be either returned at the end or used to do more complicated calculations. Using variables make the code more modular, allowing you to make changes more easily as the code becomes longer and more compex.

VAR table_temp = SUMMARIZE(CALCULATETABLE(FILTER( Sales, Sales[EOM] > [Start Date])))

Examples of Use of Variables in DAX code

Revenue WoW change % =
VAR selv = IF(HASONEFILTER(dim_date[wn]), SELECTEDVALUE(dim_date[wn]), MAX(dim_date[wn]))
VAR revcw = CALCULATE([Revenue],dim_date[wn]= selv)
VAR revpw = CALCULATE([Revenue],FILTER(ALL(dim_date),dim_date[wn]= selv-1))
RETURN
DIVIDE(revcw,revpw,0)-1

post_invoice_deductions_pct =
VAR res = calculate(max(post_invoice_deductions[discounts_pct]), RELATEDTABLE(post_invoice_deductions))
RETURN res*fact_actuals_estimates[net_invoice_sales_amount]