Question

An analytics engineer is optimizing a slow DAX measure in an enterprise semantic model. The measure computes a ratio and references the same base expression [Total Sales] four separate times: once in the numerator, once in the denominator, and twice inside conditional logic that returns BLANK when sales are zero or negative. Profiler traces show the storage engine re-scanning the fact table multiple times per visual. Which change will most reduce redundant evaluation while preserving the result?

Accepted Answer

Store [Total Sales] once in a VAR at the start of the measure and reference that variable in the numerator, denominator, and conditional checks

Answer

Wrap each reference to [Total Sales] in a separate CALCULATE with ALL() to force a single filter context

Answer

Convert the measure into a calculated column so the ratio is computed once at refresh time

Answer

Replace [Total Sales] references with SUMX iterating over the fact table in each location