VAR
Stores a value or a table in a named variable that the RETURN part can reuse.
VAR <name> = <expression> [VAR <name> = <expression> ...] RETURN <expression>Returns: Whatever the RETURN expression returns.
What it does
VAR names an intermediate result, so a formula reads like a set of steps and each calculation runs once. A typical use takes total sales and total cost, then computes margin and margin percent from the named pieces. The rule to learn is that a variable is evaluated where it is written. Its value does not change when a later CALCULATE changes the filter.
Syntax and arguments
- VAR name = expression: one or more variables, each on its own line. Names have no spaces and should describe the value.
- RETURN expression: the final result. Every formula that uses VAR must have a RETURN.
- A variable can hold a single value or a table.
Example
Gross margin percent, with the pieces named so an auditor can follow the logic.
Gross Margin % =
VAR Revenue = [Total Sales]
VAR Cost = [Total Cost]
VAR Margin = Revenue - Cost
RETURN
DIVIDE ( Margin, Revenue )
Each measure is evaluated once, in the current context. The formula reads top to bottom. DIVIDE returns blank when revenue is zero, instead of an error.
Example: a second use
Count the stores above a threshold, with the threshold and the store list each set once.
Stores Over Threshold =
VAR Threshold = 1000000
VAR StoreList = VALUES ( Stores[StoreName] )
RETURN
COUNTROWS (
FILTER (
StoreList,
[Total Sales] > Threshold
)
)
The variable StoreList holds a table, and FILTER loops over it. The threshold is written once, so changing it means editing one line.
Things to watch
- A variable is evaluated where it is written. A variable defined outside
CALCULATEignores a filter thatCALCULATEadds, because its value was already calculated. Put the measure inside theCALCULATEwhen the filter must apply. - Only the RETURN expression produces the result. A variable on its own cannot be the result.
- Variables are local to one formula. They cannot be shared across measures.
- Blank values carry through. A variable holding BLANK makes most comparisons false.
- Variables make formulas easier to test: return one variable at a time to see its value.
In business terms
Almost every governed measure uses variables: margin, budget variance, variance percent, aging buckets and allocation shares. Named steps let a reviewer check each piece of the logic in a formula review.