TL;DR
Large Power BI models slow down for two reasons: the Formula Engine is doing too much row-by-row work, or the Storage Engine is fighting an oversized, high-cardinality model. Diagnose which one first using Performance Analyzer and DAX Studio, then fix it: rewrite CALCULATE and FILTER patterns, use variables to cut repeated evaluations, and reduce cardinality where DAX alone can’t help.
Your Power BI model probably worked fine at two million rows. Then the fact table grew, more measures got added, and a report that used to open in two seconds now takes fifteen. Nothing about the DAX changed, but the model around it did, and that is usually where large-model performance problems start.
Most DAX advice stops at syntax. You already know how to write CALCULATE, FILTER, and SUMX. What is harder to find is guidance on which of your existing patterns quietly force the Formula Engine to iterate row by row, or which calculated column is inflating your VertiPaq dictionary without anyone noticing until a refresh starts timing out.
This post gives you a diagnostic method for separating Formula Engine problems from Storage Engine problems, a set of advanced DAX rewrites with before and after code, and a model-level checklist for the bottlenecks that no measure rewrite will ever fix. If your team wants the wider context first, Addend Analytics’ analytics maturity model overview is a useful starting point before going this deep into DAX tuning.
The Root Causes Behind Slow DAX Measures
Most performance problems in large Power BI models are not really DAX problems. They are model design problems that DAX happens to expose. A measure that ran fine against 500,000 rows can become unusable at 50 million rows, not because the DAX got worse, but because the underlying columns, relationships, and calculation patterns were never built to survive that scale.
Four patterns show up again and again in slow large models:
- Iterators wrapped around iterators, where SUMX calls a measure that itself contains another SUMX or FILTER, multiplying row-by-row work.
- High-cardinality columns such as GUIDs, raw timestamps, or free-text fields that are hard for VertiPaq to compress and expensive to scan.
- Filter logic built with nested FILTER(ALL(Table)) instead of native CALCULATE filter arguments, which forces the Formula Engine to materialize entire tables.
- Calculated columns doing work that Power Query or the source system could have done once, at refresh time, instead of DAX doing it on every query.
A high-cardinality column’s dictionary can account for more than 90 percent of that column’s total storage cost in VertiPaq. SQLBI, “Optimizing High Cardinality Columns in VertiPaq”
None of these problems announce themselves clearly. A report just feels slow, and the instinct is often to rewrite whichever measure is on the screen. Before doing that, it helps to know whether the problem is actually in that measure at all.
How to Know What’s Actually Slow Before You Fix It
Power BI Desktop includes two built-in tools worth using before reaching for anything external. Performance Analyzer, under the View ribbon, records how long each visual takes to refresh and breaks that time into DAX query, visual display, and other categories. DAX Query View lets you run and inspect the exact query behind a visual without leaving Power BI.
Performance Analyzer tells you which visual is slow. It does not tell you why. For that, most experienced Power BI developers export the query and open it in DAX Studio, a free tool that connects to your model and shows engine-level detail that Performance Analyzer does not expose.
DAX Studio’s Server Timings feature separates query execution into Formula Engine time and Storage Engine time, showing exactly where the engine spends its effort. Microsoft Learn, “Use Performance Analyzer to Diagnose Issues”
That Formula Engine / Storage Engine split matters because the fix for one is almost never the fix for the other. Rewriting a measure will not help if the real cost is sitting in an oversized column dictionary, and adding an aggregation table will not help if the real cost is a nested iterator.
The Five-Step Loop for Diagnosing DAX Bottlenecks
Addend Analytics’ Power BI engineers use a five-step loop to keep diagnosis and rewriting separate, so time is not spent optimizing the wrong thing. It works the same way whether the model has ten tables or a hundred.
1. Capture
Record the slow visual in Performance Analyzer and copy its underlying DAX query.
2. Split
Run that query in DAX Studio with Server Timings enabled to see the Formula Engine and Storage Engine split.
3. Isolate
Check the query plan for CallbackDataID operations, which signal the Formula Engine calling back to the Storage Engine row by row.
4. Rewrite
Target whichever engine is dominant. A Formula Engine problem usually needs a DAX pattern change; a Storage Engine problem usually needs a model change.
5. Reverify
Re-run the same query in DAX Studio and compare the new Formula Engine / Storage Engine split against the original, not just the total time.
Reverifying against the same benchmark matters more than it sounds. A measure can look faster in Power BI Desktop simply because the result is cached, while the underlying query cost has not actually changed.
NEXT STEP
Trying to work out whether your model’s slowdown is a DAX problem or a model design problem? Addend Analytics’ Power BI and data analytics consulting team runs this kind of diagnostic against production models regularly.
Advanced DAX Techniques for Faster Measures
Once you know which engine is dominant, three DAX patterns account for most of the fixable Formula Engine cost in large models: filter arguments instead of nested FILTER, right-sized iterators, and controlled context transition.
Filter arguments instead of nested FILTER(ALL())
Before:
Sales Amount London :=
CALCULATE (
SUM ( Sales[Amount] ),
FILTER ( ALL ( Sales ), Sales[Region] = “London” )
)
After:
Sales Amount London :=
CALCULATE (
SUM ( Sales[Amount] ),
Sales[Region] = “London”
)
The first version forces the Formula Engine to materialize the entire Sales table through FILTER before CALCULATE can use the result. The second version passes the condition as a native filter argument, which the Storage Engine can apply directly during its scan. On a large fact table this is often the single highest-impact rewrite available, and it costs nothing in readability.
Iterators only where iteration is actually needed
SUMX exists for row-by-row calculations that cannot be expressed as a simple aggregation, such as quantity multiplied by price per line item. Using SUMX to wrap a plain SUM, or to re-implement filtering that CALCULATE already handles natively, is common and expensive. It asks the Formula Engine to do work the Storage Engine could have done on its own.
Context transition inside iterators
Every CALCULATE or measure reference inside an iterator such as SUMX triggers context transition, converting the current row into a filter context. That is necessary when the calculation genuinely depends on row context. Calling a measure inside SUMX when a direct column reference would do the same job multiplies evaluation cost across every row of the iteration.
How Variables Reduce DAX Calculation Cost
VAR and RETURN are the two keywords with the best ratio of effort to performance gain in DAX. A variable is computed once and reused, instead of being recalculated every time the same expression appears in a formula.
STAT
“DAX variables, introduced into the language in 2015, let the engine compute a repeated sub-expression once instead of re-evaluating it every time it appears in a formula.”
Before:
YoY % :=
IF (
NOT ISBLANK ( [Sales Amount] ) && NOT ISBLANK ( [Sales PY] ),
DIVIDE ( [Sales Amount] – [Sales PY], [Sales PY] )
)
After:
YoY % :=
VAR CurrentSales = [Sales Amount]
VAR PriorSales = [Sales PY]
RETURN
IF (
NOT ISBLANK ( CurrentSales ) && NOT ISBLANK ( PriorSales ),
DIVIDE ( CurrentSales – PriorSales, PriorSales )
)
In the first version, [Sales Amount] and [Sales PY] are each evaluated twice: once inside the ISBLANK checks and again inside DIVIDE. The variable version evaluates each measure exactly once and reuses the stored value, which matters more as the measures behind CurrentSales and PriorSales grow more complex.
Variables defined before an IF or SWITCH branch are still evaluated regardless of which branch executes, which can quietly reduce the benefit of short-circuit evaluation if used without care. SQLBI, “Optimizing IF and SWITCH Expressions Using Variables”
That caveat is worth sitting with. Variables are not a universal performance switch. Declaring a variable inside the branch that actually needs it, rather than before the IF, preserves the Formula Engine’s ability to skip work the branch never requires.
Beyond DAX: Fixing Model-Level Performance Limits
Some slowdowns cannot be fixed in the measure at all, no matter how well it is written. These live in the model itself: column cardinality, calculated columns, unused fields, and storage mode.
- High-cardinality columns such as GUIDs or full timestamps can be split into smaller-range columns at the source or in Power Query, shrinking their VertiPaq dictionaries substantially.
- Calculated columns are stored less efficiently than columns loaded through Power Query, and they are rebuilt on every full refresh, which can extend refresh windows on large tables.
- Columns imported but never used in a measure, visual, or relationship still consume VertiPaq memory and should be removed rather than kept just in case.
- Storage mode, whether Import, DirectQuery, or Direct Lake, sets a ceiling on how much a DAX rewrite alone can achieve once a fact table reaches tens of millions of rows.
Splitting a 100-million-row TransactionID column into two smaller-range columns cut its VertiPaq storage cost from roughly 3 GB to under 200 MB, a reduction of more than 90 percent. SQLBI, “Optimizing High Cardinality Columns in VertiPaq”
Choosing the right optimization approach
| Approach | What It Fixes | Effort | When to Use It |
| DAX pattern rewrite | Formula Engine cost from nested iterators or filter logic | Low to medium | First response to a single slow visual or measure |
| Cardinality and calculated column cleanup | VertiPaq memory and Storage Engine scan time | Medium | Model feels heavy everywhere and refresh times are creeping up |
| Storage mode or aggregation change | Query concurrency and scale beyond tens of millions of rows | High | DAX and model cleanup alone no longer hold performance |
What this table means for you: start with the DAX rewrite because it is the fastest to test, but budget time for model-level cleanup if the problem shows up on multiple reports at once, not just one visual.
How Addend Analytics Tackles Slow Power BI Models
Most performance engagements at Addend Analytics start the same way this post does: separate the diagnosis from the fix before touching a single measure. That discipline matters more in large, multi-team models, where a change made to solve one visual’s slowness can quietly break a dozen others.
- Model and DAX audits that run the Two-Engine Diagnostic Loop against your slowest reports before recommending any rewrite.
- Refactoring existing measures and calculated columns without changing the numbers users already trust.
- Model redesign work, including storage mode and aggregation strategy, for fact tables that have outgrown DAX-level fixes alone.
- Handover documentation so your own developers can run the same diagnostic loop on the next slow report, rather than depending on an outside team every time.
This work sits alongside Addend Analytics’ broader data analytics consulting services and its analytics strategy and roadmap consulting, for teams that need performance fixed as part of a larger Power BI or Microsoft Fabric rollout rather than as a one-off engagement.
7 Checks Before Publishing a Large Power BI Model
Run this checklist before a large model goes to production, not after users start complaining.
- Every visual on the report loads in Performance Analyzer without any single DAX query taking more than a few seconds.
- No measure contains a nested FILTER(ALL()) pattern that could be a native CALCULATE filter argument instead.
- Expressions repeated more than once in the same measure are captured in a VAR instead of being re-evaluated.
- Iterators such as SUMX are used only where row-by-row calculation is genuinely required.
- High-cardinality columns not needed for filtering or display have been removed or split.
- Calculated columns duplicating logic already available in Power Query have been converted or removed.
- Storage mode has been reconsidered if the largest fact table is approaching tens of millions of rows.
Frequently Asked Questions
SUMX and RANKX are the ones to watch, because they test every row in a table and can scale poorly as row counts grow. DISTINCTCOUNT over high-cardinality columns is another common source of slow queries, since VertiPaq has to track every unique value. Frequently Asked Questions
SUMX and RANKX are the ones to watch, because they test every row in a table and can scale poorly as row counts grow. DISTINCTCOUNT over high-cardinality columns is another common source of slow queries, since VertiPaq has to track every unique value. Frequently Asked Questions
SUMX and RANKX are the ones to watch, because they test every row in a table and can scale poorly as row counts grow. DISTINCTCOUNT over high-cardinality columns is another common source of slow queries, since VertiPaq has to track every unique value. Choosing Your Next Step in DAX Optimization
The measures on your report are rarely the whole story behind a slow Power BI model. Diagnosing which engine is actually doing the work, Formula Engine or Storage Engine, decides whether the fix belongs in the DAX or in the model. Skipping that step is how teams end up rewriting the wrong thing, or adding capacity that never helps.
Start with the Two-Engine Diagnostic Loop on your slowest visual, then work through the pre-publish checklist before the next large model ships. If your team is weighing whether a performance problem needs a DAX rewrite or a deeper model redesign, see how Addend Analytics has approached Power BI performance work for other clients.
NEXT STEP
If a large model’s performance is affecting how much your team trusts the numbers, it is worth having someone look at the model before committing to a rebuild. Get in touch with Addend Analytics.
Author By
Kamal Sharma
Kamal brings over 20 years of experience in data analytics and business intelligence. He has led the design and implementation of analytics solutions across operations, financial reporting, and performance improvement initiatives. With a background in business statistics and Six Sigma, his work focuses on applying data in a structured and practical way to solve real business challenges.
