DAX Formula Optimization - Using Calculate vs. another measure

[Anonymous]

Hi, I'm just wondering if there is any performance difference in using a simple calculate vs. a measure you've already created in a new DAX formula?

i.e. say we have the measures:

[$ Sales] = SUM(...)

[# Sales] = SUM(...)

[$ Sales LY] = CALCULATE(......)

[# Sales LY] = CALCULATE(......)

[Sales Rate] = [$ Sales] / [# Sales]

Which would be faster? Or is the difference minimal?

Sales Rate LY = CALCULATE([Sales Rate], 'Date'[Year] = "2016")

Sales Rate LY = [$ Sales LY] / [# Sales]

[Anonymous · Accepted solution]

I would expect that in your example, your calculate statement should be faster.  This is because it only needs to make considerations on the table once.

How much more optimal?  How long is a peice of string?  It really comes down to the size of your data and what relies on these measures.  Is your table 1 million rows and is your visuals going to be calling this figure 100s of times?

To further the optimisation discussion, you can also make use of Variables to increase the efficency of a measure.  Consider this:

Fail % = DIVIDE(
           [No Of Fails],
           [No Of Fails] + [No Of Success]
)

Compared to:

Fail % = Var Fails = [No Of Fails]
RETURN
DIVIDE(
           Fails,
           Fails + [No Of Success]
)