Using Variables in DAX

If you're used to working in Excel you're probably used to writing long formulae. This can be a really complex job, but auditing it when something goes wrong or needs to change can be harder still.

In Power BI the need to write complex formulae is the same, and the length of them can be more, but there is a DAX function that allows you to break down complex formulae into sections, the Variables Function. These variables can each be shown and calculated separately and then used to return a result.

If you have a formula made up of several sub-formulae, you can break them up using the Variables Function. For Example, if I have a year on year actual change I may have written some DAX like this, using the Current Year and Year Ago measures that I had previously created:

SALES REVENUE CHG VS YA =
    Sales Data'[Sales Revenue TY] - 'Sales Data'[Sales Revenue YA

I appreciate that this isn't the longest or most complex formula, but does help us understand the context and reason for using variables. This formula is working out two parts to return the value for change vs a year ago.

By changing this to use the Variables Function I would add a variable for this year's sales, which I can call 'TY' and a variable for last years sales, which I can call 'YA'. You can use your own reference for the variables so that they make sense to you.

Then we need to add a 'Return' section into the DAX so that it knows what to actually return as the formula result.

This changes our formula to:

SALES REVENUE CHG VS YA =
    VAR TY = 'Sales Data'[Sales Revenue TY]
    VAR YA = 'Sales Data'[Sales Revenue YA]

RETURN
    TY - YA

This makes the formula much easier to read and much easier to audit should we need to change anything.

There is one final change that we could make to this if we don't want to have separate formulae elsewhere for the sales of This Year and the sales of Last Year. We can bring those calculations into the variables and therefore not have them created elsewhere.

This may change this measure to look like this:

SALES REVENUE CHG VS YA =
    VAR TY = SUM([SALES REVENUE])
    VAR YA =
        CALCULATE (
            SUM('Sales Data'[SALES REVENUE]),
            SAMEPERIODLASTYEAR(Dates[DATE]
            )

RETURN
    TY - YA

This now contains two different measures as variables within the measure and saves the need of having them created elsewhere in the model.

To summarise, using variables can make complex measures easier to read, easier to audit, and the use of variables also changes how Power BI calculates and stores the results of a variable, resulting in improved performance.