Variables & DIVIDE
Power BI · Advanced DAX & Time Intelligence
Theory
Variables let you name an intermediate result inside a DAX expression. You declare them with VAR name = expression, and finish with RETURN followed by the final expression. Each variable is evaluated once, in the context where it is defined, and then behaves like a constant, which makes long measures easier to read, avoids repeating the same calculation, and often improves performance. Variables are also very handy for debugging: temporarily RETURN a variable to see its value.
DIVIDE(numerator, denominator, alternate result) performs safe division: when the denominator is zero or blank it returns blank, or the alternate result you supply, instead of an error or infinity. For example, Avg Order Value with VAR TotalSales = [Total Sales] and VAR OrderCount = DISTINCTCOUNT(Sales[OrderID]) returns DIVIDE(TotalSales, OrderCount): North ₹3,200 / 3 = ₹1,066.67, West ₹1,200 and South ₹600.
Why we use it
Readable measures are easier to trust and maintain, and safe division prevents ugly errors in visuals when a filter leaves no rows. Both are habits interviewers look for.
When to use it
Use variables in any measure that reuses a value or has more than one logical step. Use DIVIDE for ratios where the denominator can be zero or blank. The / operator is fine when the denominator is a constant or can never be zero.
How it works
- Measure has several steps
- Declare each step as a VAR
- Each VAR is evaluated once in the current context
- Is there a division by a value that may be 0 or blank? Yes: Use DIVIDE with an optional alternate result. No: Use the / operator.
- RETURN the final expression
- Readable, safe measure
Syntax
VAR <name> = <expression>
[VAR <name2> = <expression>]
RETURN <result_expression>
DIVIDE(<numerator>, <denominator>[, <alternateresult>])Example
Avg Order Value =
VAR TotalSales = [Total Sales]
VAR OrderCount = DISTINCTCOUNT(Sales[OrderID])
RETURN
DIVIDE(TotalSales, OrderCount)
// Variables are evaluated where they are defined:
Sales vs North =
VAR NorthSales = CALCULATE([Total Sales], Sales[Region] = "North")
RETURN DIVIDE([Total Sales], NorthSales) // West: 2,400 / 3,200 = 0.75Key points
- VAR ... RETURN names intermediate results
- A variable is evaluated once, where it is defined
- A variable does not change if CALCULATE later changes the context
- DIVIDE returns blank (or an alternate value) on zero or blank denominators
- RETURN a variable temporarily to debug a measure