Advanced DAX Optimization: How Do You Improve Performance in Complex Power BI Semantic Models?

Hi binitafulpagare​ ,

Thank you for reaching out to Microsoft Fabric Community Forum, Below are the few points which can resolve your issue. Let us know if you need any further assistance.

• Avoid unnecessary nested CALCULATE(), FILTER(), and SUMX() operations. Prefer Boolean filter conditions and native aggregations such as SUM() wherever possible.
• Use DAX variables (VAR) to store intermediate calculations, avoid repeated expressions, and reduce unnecessary evaluations.
• Carefully manage filter context using ALL(), REMOVEFILTERS(), ALLEXCEPT(), and KEEPFILTERS(). Validate context transition and relationship filter propagation.
• Follow a star schema with separate fact and dimension tables. Prefer one-to-many relationships and single-direction filtering. Avoid unnecessary bidirectional and many-to-many relationships.
• Use Power BI Performance Analyzer to identify slow visuals and distinguish DAX query execution time from visual rendering time.
• Use DAX Studio to analyze Server Timings, Formula Engine and Storage Engine operations, and query plans to identify expensive calculations and repeated scans.
• Optimize VertiPaq memory consumption by removing unused columns, reducing high-cardinality fields, using appropriate data types, and avoiding unnecessary calculated columns.
• Implement incremental refresh and aggregation tables for large fact tables to reduce refresh processing and improve query performance.
• Select the appropriate storage mode: Import for fast in-memory reporting, DirectQuery for specific near-real-time requirements, and Direct Lake for suitable Fabric workloads. Evaluate composite models where necessary.
• Establish performance benchmarks, monitor capacity CPU and memory utilization, and optimize the slowest measures first. Validate results across slicers, filters, date ranges, and RLS roles to ensure business logic and calculation accuracy remain unchanged.

 First identify whether the performance bottleneck is caused by DAX execution, semantic model design, VertiPaq memory usage, visual rendering, or capacity limitations. Then optimize the highest-impact areas individually and validate the results before deploying the changes.

Thanks,
Chaithanya.

binitafulpagare​ Hi! I work at Microsoft, and I'd recommend approaching this as an end-to-end semantic model optimization exercise, rather than focusing exclusively on DAX.

With 50+ measures and 20M+ rows, the challenge isn't necessarily the dataset size itself, but how the model is structured, how filters propagate, and how queries are executed.

Here's the systematic approach I'd suggest:

1. Identify the actual bottleneck first

Before modifying any DAX, start with Power BI Performance Analyzer to identify which visuals are responsible for the 15–20 second rendering times.

Then use DAX Studio, particularly Server Timings and Query Plan, to investigate the expensive queries.

The key distinction is understanding whether the bottleneck is:

• Formula Engine (FE): expensive calculations, complex iterators, or intermediate results.
• Storage Engine (SE): large scans, expensive aggregations, or inefficient filtering.
• Visual rendering or DirectQuery latency: where optimizing DAX alone might not solve the issue.

This helps avoid optimizing measures that aren't actually contributing to the performance problem.

2. Optimize DAX without changing business logic

A few techniques I'd prioritize:

• Use VAR to avoid unnecessarily repeating expensive calculations.
• Prefer Boolean filter expressions in CALCULATE() over FILTER() when the logic allows it.
• Avoid iterating over large fact tables when the same calculation can be performed at a more aggregated grain.
• Review nested iterators and context transitions, especially when SUMX() evaluates complex measures repeatedly.
• Use REMOVEFILTERS() or ALL() deliberately, ensuring that the intended filter context is preserved.

For example, instead of:

CALCULATE([Total Sales], FILTER(Sales, Sales[Status] = "Completed"))

Consider:

CALCULATE([Total Sales], KEEPFILTERS(Sales[Status] = "Completed"))

When the intended semantics are equivalent, this can simplify the filter expression and potentially improve execution efficiency.

Importantly, I would validate the results before and after each optimization, particularly for calculations involving context transition or complex filter propagation.

3. Review the semantic model architecture

For models with multiple fact tables, I'd generally recommend a star schema with shared conformed dimensions, where appropriate.

In particular:

• Avoid unnecessary bidirectional relationships.
• Review many-to-many relationships and ambiguous filter paths.
• Use dimension tables for filtering and grouping.
• Remove unused columns and reduce unnecessary high-cardinality attributes.

For large Import models, I'd also use VertiPaq Analyzer to identify columns consuming disproportionate amounts of memory.

4. Evaluate the storage mode

Import, DirectQuery, and Direct Lake address different architectural requirements.

• Import: Often provides excellent interactive performance through VertiPaq compression, provided the model fits within capacity and refresh requirements.
• DirectQuery: Useful when data must remain in the source, but performance depends heavily on source optimization, query folding, and network latency.
• Direct Lake: Particularly interesting for Microsoft Fabric architectures, as it enables querying Delta tables in OneLake without a traditional Import refresh.

However, Direct Lake is not automatically the best option for every workload. Capacity sizing, model design, and potential DirectQuery fallback behavior must also be considered.

5. Optimize incrementally and measure the impact

For your specific scenario, I'd follow this sequence:

1. Capture a performance baseline for the slowest visuals.
2. Identify the most expensive DAX queries using Performance Analyzer and DAX Studio.
3. Investigate FE versus SE execution time.
4. Optimize the most expensive measures individually.
5. Review relationships, cardinality, and VertiPaq memory usage.
6. Reassess the storage mode only if the existing architecture remains a limiting factor.
7. Compare execution times and calculation results before and after each change.

One important recommendation: I wouldn't start by rewriting all 50 measures.

I'd first identify the few queries responsible for most of the execution time and optimize those. Often, improvements to a shared base measure or relationship structure can benefit multiple visuals simultaneously.

The goal should be to reduce the amount of data processed and the computational work required, while preserving exactly the same business results.

Hope this helps!
BBF

Hi binitafulpagare​,

For a model like this, I would avoid starting by rewriting all 50 measures. I would first identify whether the delay is coming from the visual, the DAX query, the model design, or the storage mode.

My usual first step is Power BI's Performance Analyzer. It shows which visuals are actually slow and lets you copy the DAX query generated by each one. That gives you a much better starting point than guessing which measure is expensive.

Once you have the slow query, I would test it with Server Timings in DAX Studio and look at whether most of the time is being spent in the Formula Engine or Storage Engine. Heavy Formula Engine time usually points me toward iterators, row-by-row FILTER logic, repeated context transitions, or complex branching. Heavy Storage Engine time makes me look more closely at cardinality, relationships, filters, and how much data the query is scanning.

Before changing DAX, I would also inspect the model itself. Microsoft's star schema guidance strongly recommends separating fact and dimension tables with clear one-to-many relationships. I would minimize many-to-many and bi-directional relationships unless they are genuinely needed, because Microsoft's relationship guidance notes that bidirectional filtering can hurt both performance and model clarity.

For the DAX itself, I usually look for FILTER over large fact tables, nested iterators such as SUMX inside FILTER, repeated CALCULATE expressions, and calculations that could be expressed with simpler filter arguments. I would also use variables for repeated expressions and push filtering onto dimensions where possible rather than scanning the fact table repeatedly.

For VertiPaq, cardinality matters more than raw row count. IDs, long text, high-precision timestamps, GUIDs, and columns that are never used can consume a surprising amount of memory. Removing unused columns, splitting datetime values where appropriate, and reducing unnecessary precision can often improve both compression and query speed.

On storage mode, I would not assume DirectQuery or Direct Lake is automatically better because the model is large. Import is still extremely fast when the model fits comfortably in memory. DirectQuery shifts more work to the source and depends heavily on source performance. Direct Lake can perform very well for Delta data in OneLake, but Microsoft's Direct Lake query guidance also recommends checking whether queries are falling back to DirectQuery, because that can materially change performance.

So for your 15-20 second visuals, I would work in this order:

• identify the slow visual with Performance Analyzer
• capture and benchmark its DAX query
• separate Formula Engine time from Storage Engine time
• fix obvious model issues such as high-cardinality columns or unnecessary relationship complexity
• then simplify the specific DAX expression causing the bottleneck
• and only after that reconsider storage mode or capacity

That way you change one bottleneck at a time and can verify that each optimization actually improved performance without changing the business result.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.