DAX Query Performance vs Nested Measures

[WishAskedSooner]

Hi Experts!

I have run into a snag. I am using Storage Mode: Mixed. When I try to use the Performance Analyzer, nothing is recorded. I assume that is because it is incompatible with Storage Mode: Mixed.

Anyway, I have a number of measures that are deeply nested (3 or 4 deep). This helps me keep track of the underlying logic which is quite complex. However, I have begun to notice some latency in the calculations.

I have heard that getting rid of nesting in a measure speeds things up. Can anyone confirm whether this is actually true?

I want to know before embarking on unnesting my measures which will be a significant  effort.

TIA

[v-kpoloju-msft]

Hi @WishAskedSooner,

Thanks for raising this. Also, thanks to @Ritaf1983, @collinq, for those inputs on this thread. I understand you are using Mixed Storage Mode and facing performance issues due to deeply nested DAX measures, and you are looking to confirm if unnesting them could improve query performance.

Use DAX Studio to Analyse Performance: Since Performance Analyzer is limited in Mixed Mode. Use DAX Studio to Track server timings. Measure query duration. Identify bottlenecks (e.g., heavy usage of nested IF/VARs, context transitions).

Understand the Cost of Nested Measures: In general, nesting measures does not inherently slow down performance, unless: They involve repeated context transitions (e.g., CALCULATE, FILTER, RELATEDTABLE, etc.). There is redundant evaluation of the same logic multiple times. However, in Direct Query, each nested measure can result in an additional SQL query, making nesting more costly.

Best Practice: Flatten Critical Measures: For performance-critical calculations (especially on report visuals with latency). Try flattening the logic into one optimized measure. Avoid reusing intermediate measures if they add overhead.

Use Hybrid Tables Where Possible: If certain tables can be mostly used in Import mode, consider splitting them or changing them to Import to avoid the DQ overhead entirely.

Kindly refer to the below mentioned link for better understanding:
Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Micr...

Also, when flattening or optimizing your measures, follow DAX variable best practices to reduce unnecessary recalculations and improve readability:
Best practices for DAX variables

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Fabric Community Forum.

[WishAskedSooner]

Thank you for all the great suggestions! I also appreciate the insight on DAX Studio vs Performance Analyzer.

Optimizing will be a project in itself. Just need to prioritize and allocate time now.

[v-kpoloju-msft · Accepted solution]

Hi @WishAskedSooner,

Glad to hear the suggestions were helpful. 

When you get to the optimization phase, you might find these Microsoft resources handy for planning and prioritizing improvements:

Covers data model design best practices, column/cardinality tips, and storage mode considerations. Optimization guide for Power BI - Power BI | Microsoft Learn

Step-by-step guide on capturing and interpreting performance metrics. Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Micr...

This way, when you have the time to focus on optimization, you will have a solid starting point.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[v-kpoloju-msft]

Hi @WishAskedSooner,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @WishAskedSooner,

Hope you had a chance to try out the solution shared earlier. Let us know if anything needs further clarification or if there's an update from your side always here to help.

Thank you.

[v-kpoloju-msft]

Hi @WishAskedSooner,

Just wanted to follow up one last time. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[WishAskedSooner]

I wanted to reply that I only very recently flattened my measures. And here are my findings:

Pros:

1. Flattening dramatically reduced the number of measures. So that was good.

2. Performance improvement was modest. Wish it was more.

3. Optimization is easier now that all the code is one measure. So, hopefully I can squeeze more gains out of it.

Cons

1. Time consuming.

2. In some ways maintenance is harder due to duplication, yet in others it is easier due to the reduced number of measures.

The bottomline is that production ready code necessitates flattening, but it comes with a cost.

[v-kpoloju-msft]

Hi @WishAskedSooner,

Thanks for sharing your experience and detailed findings that’s valuable insight. It’s great to hear that flattening your measures helped streamline your model and provided some performance improvement, even if modest for now.

You have also made an excellent point about the trade-off between maintenance effort and optimization potential. It’s often a balance between readability, reusability, and performance especially in production environments.

If you are looking to further improve performance, you might consider: Reviewing DAX query plans or using Performance Analyzer to identify bottlenecks. Checking if certain calculations can be pre-aggregated or handled in Power Query. Leveraging calculation groups to minimize duplication where possible.

Thanks again for sharing your learnings this kind of feedback helps others in the community facing similar optimization choices.

[Ritaf1983]

Hi @WishAskedSooner 

Like the Italians say – "It depends."

Nesting measures 3 or 4 levels deep isn’t a problem by itself. What does matter is what those nested measures are doing. If they contain expensive logic – like virtual tables, complex FILTER conditions, or heavy iterators (e.g. SUMX over FILTER) – then yes, performance can suffer.

But if each measure is relatively light (simple CALCULATEs, basic aggregations, context-preserving logic), unnesting won’t gain you anything noticeable. In fact, it might make your model harder to maintain for no real benefit.

Before you start rewriting everything, test the actual cost. DAX Studio is more reliable than Performance Analyzer in Mixed Storage Mode.

Details:
https://www.sqlbi.com/articles/optimizing-dax-expressions-involving-multiple-measures

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[collinq]

Hi @WishAskedSooner ,

I am sorry to say that, yes, having nested measures CAN cause performance slowdown.  As always, this is based on the types of functions in teh nesting and the volume of data.  For example, if you are using "Sum" and "Sumx" in a lot of them you can end up costing memory / performance becuase of the way the Power BI engine works through the nesting.

If you use a lot of variables that might help out.  

You couldn't use Performance Analyzer but before going through the pain of undoing everything, I would see if you can use DAX Studio and if it can help you find some issues.