Dax Me Anything

Using Variables in DAX improves the readability of your code by making it easier to follow the logic and structure of calculations. Additionally, variables can enhance performance in certain scenarios, as they allow calculations to be stored and reused within a measure or calculated column, reducing redundancy.

This guide assumes you already have a grasp of core DAX concepts. But don’t worry — if you’re unfamiliar, here’s what you might want to brush up on:

• Power BI: DAX Filter Context
• Power BI: DAX Row Context
• Power BI: Measures vs Calculated Columns
• Power BI: DAX Calculate Function

Let us illustrate how this works with a real-world example.

---

Explanation Through Story Telling:

Meet Timmy, an entrepreneurial elementary school student running a marble-selling business.

Timmy had just returned from a two-week vacation with his family when, to his dismay, all his measures looked like gibberish. He even began to question whether he had written them himself. Lately, he had been reading online that using variables could make DAX measures more organized and easier to understand. So, he decided to rewrite his measures and calculated columns to improve their readability.

One of the Measures Timmy wanted to update was the Average Revenue by Customer.

Measure without Variables:

Average Revenue By Customer =

DIVIDE(
    SUMX('Order Items', 'Order Items'[sale_price] * 'Order Items'[count]),
    DISTINCTCOUNT(Customers[customer_id])
)

Same Measure with Variables:

Average Revenue By Customer With Variables =

VAR total_sales = SUMX('Order Items','Order Items'[sale_price]* 'Order Items'[count])

VAR unique_count_of_users = DISTINCTCOUNT(Customers[customer_id])

RETURN  DIVIDE(
    total_sales,
    unique_count_of_users
)

Adding variables improves code readability by clearly outlining each step. For example, it’s immediately apparent that the goal is to divide the total sales by the unique count of users to calculate the average revenue per customer.

Note: Both Measures End up giving identical results.

Note: variables in DAX are calculated only once at the point of their definition. Once computed, reusing them in different contexts does not trigger recalculation. Let’s explore this concept further with an example.

Timmy wanted to calculate the % of sales made by each of the marble colors compared to total sales, he proceeded to create this measure:

Percentage of Revenue =

DIVIDE(
    SUMX('Order Items','Order Items'[sale_price]* 'Order Items'[count]),

    CALCULATE(
        SUMX('Order Items','Order Items'[sale_price]* 'Order Items'[count]),
        ALL('Order Items'[color])
    )

)

In this measure Timmy divides:

• The sum of sales per color, as affected by the color filter set in the visual.
• By the total sum of sales using ALL function to ignore any filter set to the color column by the visual.

Timmy noticed that the SUMX formula is repeated twice identically, so he decided to create a variable to save calculation time and to make the code become more readable.

Timmy proceeded to create a new version of the measure with variables:

Percentage of Revenue with Variables =
Var revenue = SUMX('Order Items', 'Order Items'[sale_price] * 'Order Items'[count])

RETURN DIVIDE(
    revenue,
    CALCULATE(
        revenue,
        ALL('Order Items'[color])
    )
)

Timmy added each measure to a Card visual and placed them side by side, to his surprise, the measure with variables had all the percentage set to 100 %, so what happened?

The measure produced an inaccurate result because the variable was calculated only once at the point of its definition:

---

Overall DAX Variables are a very important tools that make your DAX code look cleaner and makes it easier to maintain and update. They also eliminate redundancy by avoiding repeated calculations. However, it’s crucial to use them correctly. Variables are calculated only once at the point of definition; after that, they are simply referenced and are no longer influenced by filters or context changes.