Came across this pattern recently and it got me thinking.
Sales YTD V1 =
CALCULATE(
[Total Sales],
DATESYTD('Date'[Date])
)
Sales YTD V2 =
CALCULATE(
CALCULATE(
[Total Sales],
DATESYTD('Date'[Date])
)
)
Both return the same number in every test I ran.
The outer CALCULATE in V2 appears to do nothing. But I can't shake the feeling there's an edge case where it would actually change the result.
Anyone been deep enough in the engine to know for sure?
This is one of the most common performance mistakes I see in DAX:
Sales Commission =
SUMX(
Sales,
Sales[Amount] - AVERAGE(Sales[Amount])
)
The problem: AVERAGE(Sales[Amount]) gets recalculated on every single row of the Sales table. On a table with 1 million rows that's 1 million separate AVERAGE calculations.
The fix:
Sales Commission =
VAR AvgSales = AVERAGE(Sales[Amount])
RETURN
SUMX(
Sales,
Sales[Amount] - AvgSales
)
VAR captures the value once outside the iterator. SUMX then reuses that single value across every row instead of recalculating it each time.
Same result. Significantly less work for the engine.
This applies to any expression inside SUMX, AVERAGEX, MAXX, MINX, COUNTAX, if you're referencing the same calculation multiple times inside an iterator, cache it in a VAR first.