cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Best Way to Calculate Dax to improve performance

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.

1 ACCEPTED SOLUTION
Super User

@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().

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Super User

@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().

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.

Super User

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.

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors