Best Way to Calculate Dax to improve performance

[MVenables]

Hi,

We are currently in a small debate regarding the way we should calculate our DAX measures. 

Currently they are calculated like the below as an example.

Sales = SUM( CustomerInvoice[Sell])

Sales TY = Calculate(
[Sales], Calendar[CurrentYear]IN {True} )

Sales LY = Calculate(
[Sales], Calendar[LastYear]IN {True} )

Sales TY var LY = [Sales TY] - [Sales LY]

Or should they be calculated as in the below method using no dependencies.

Sales TY = Calculate(
SUM(CustomerInvoice[Sell]),

Calendar[CurrentYear]IN {True}

Sales LY = Calculate(
SUM(CustomerInvoice[Sell]),

Calendar[LastYear]IN {True}

Sales TY var LY = Calculate(
Sum(CustomerInvoice[Sell]), Calendar[CurrentYear]IN {True} - 
Sum(CustomerInvoice[Sell]), Calendar[LastYear]IN {True}

Any help on this would be great thank you.

[Greg_Deckler · Accepted solution]

@MVenables Not sure of the overall performance impact of one versus the other. Generally there shouldn't be much of a performance difference, if any. Really comes down to preference in my opinion. Do you want all of your code in one place or do you want to have to hunt for it in a bunch of different places. Having all the code in a single place generally makes things easier to debug as well, especially if you use VAR. Also, be careful with the IN operator as that can potentially have performance impacts, might be better to just use = TRUE().

[MVenables]

Hi @Greg_Deckler ,

Thank you for the quick response on this. It was more just to find out if one was calculated quicker than the other or would cause issues when running the dax queries to be slower. so when doing the [Sales TY] - [Sales LY] would it cause the calculation to be slower as it is first going back to calculate the two measures stated then taking them away from each other to give the answer for example.

Thank you for the heads up on using the IN formula i will look at changing this for these Calculations. We were recommended by an external company to remove the dependencies in our Dax measures as they would cause performance issues.

[Wilson_]

MVenables,

The only reason off the top of my head on why removing dependencies in this manner would be beneficial is to prevent unintentional context transition. Since referencing a measure always wraps the calculation in a CALCULATE function, referencing other measures within an iterator (which invokes context transition) can have unintended effects on performance.

In this particular example, any performance difference should be negligible, if any.

P.S.: You can also use the performance analyzer within Power BI Desktop to test the DAX query time of your measures against each other. Just make sure to expand out the data for the visuals you are using for your test so you can see specifically the DAX-related time.