A visual that seems to “just hang” in Power BI can be dominated by either the formula engine or the storage engine—and the fix is completely different depending on which one is actually at fault. By the end of this article, you’ll know how to use DAX Studio’s Server Timings to distinguish between FE and SE bottlenecks, avoid the classic misdiagnoses, and target your optimization effort where it actually matters.
Why “Long Running” Isn’t Specific Enough: The Cost of Guessing Engine Bottlenecks
Many developers waste time optimizing the wrong layer because they mistake symptoms: a slow visual isn’t reliably caused by a slow fact table scan, nor is every DirectQuery slowness a database problem. The most common failure is to assume that “rows scanned” means storage engine is the issue, or that a complex DAX measure always means formula engine is to blame. In reality, the interplay between filter context, context transition, and cardinality often puts the opposite engine in the hot seat from what you’d expect.
- Formula Engine (FE) pain points: CALCULATE chains, complex DAX expressions, row-by-row context transitions (especially via iterators like SUMX on large tables).
- Storage Engine (SE) pain points: Large cardinality scans, expensive joins, poor model design (missing star schema), DirectQuery translation overhead.
Blindly rewriting DAX for “efficiency” or adding indexes at the data source can both miss the mark if you haven’t measured which engine is actually the bottleneck. DAX Studio is the only tool that makes this diagnosis explicit and reproducible.
DAX Studio Server Timings: What It Actually Measures (and Where People Misread It)
DAX Studio’s Server Timings pane splits out every FE and SE call when you run a query, but the summary numbers are easy to misinterpret. Many developers focus on the “Query Duration” or the “SE Duration” totals, not realizing that FE time includes both the DAX formula engine’s own work and any time it spends waiting on the storage engine. The “FE” time is not “DAX only”—it’s the total time minus all direct SE calls, which can conceal nested waits and bottlenecks.
- FE time can look high even when SE is the culprit: If a measure iterates over a high-cardinality set, each iteration may trigger a SE call, but the FE “owns” the time spent queuing and waiting for those calls to return.
- SE “Rows Scanned” is not always the villain: A high “Rows Scanned” number is only an issue if the SE duration is also high. If SE is fast, but FE is slow, the problem is likely context transition or DAX complexity, not a storage scan bottleneck.
The key is not the absolute number, but the ratio and the call pattern. Repeated short SE calls (caused by row context iteration) drive FE time up, even if the SE itself is fast. One giant SE call with high duration points to a scan or join problem.
Worked Example: SUMX on a Large Table—Where the Bottleneck Moves
Take a model with a Fact_Sales table of ~20M rows and a Dim_Product dimension. Suppose you want to calculate total discounted sales, with discounts stored in a related Dim_Discount table keyed by ProductID. Here is a naive measure:
[Total Discounted Sales Naive] :=
SUMX(
Fact_Sales,
Fact_Sales[Quantity] * RELATED(Dim_Discount[DiscountRate])
)On the surface, this looks fine: it’s a simple iterator. But run this query in DAX Studio with Server Timings, and you’ll see a suspicious pattern:
- FE duration dominates (e.g., 95% of total time), with thousands or millions of tiny SE calls.
- Each SE call fetches only the discount rate for a single row, rather than pushing the join to the SE.
This is a textbook case of context transition and row context causing the formula engine to call the storage engine once per row—devastating for performance. The naive instinct is that “SE is slow” because there are so many calls, or that “the DAX is simple so the FE should be fast.” Both are wrong: the DAX expression forces the FE to do the work that could have been pushed to the SE in a single scan.
The fix: Reshape the DAX to push as much work as possible into the storage engine. Rewrite the measure as:
[Total Discounted Sales Optimized] :=
SUMX(
ADDCOLUMNS(
Fact_Sales,
"DiscountRate", RELATED(Dim_Discount[DiscountRate])
),
Fact_Sales[Quantity] * [DiscountRate]
)Now, DAX Studio’s Server Timings will show:
- One large SE call doing a join across Fact_Sales and Dim_Discount.
- FE time drops sharply; SE time increases but is more efficient, as the join and calculation are handled in a single batch operation.
The engine bottleneck has shifted: the bulk of the work is now a single optimized storage engine operation, which can leverage VertiPaq’s strengths (or push down to the source in DirectQuery). The production of many small context transitions is eliminated.
When Storage Engine Really Is to Blame: Recognizing True Scan/Join Problems
Sometimes, the storage engine genuinely is the bottleneck—especially in DirectQuery models or with poorly optimized star schemas. The signature in DAX Studio is:
- SE duration is high and concentrated in one or two calls.
- Rows scanned is high, but FE time is modest.
For example, consider this DAX measure in a DirectQuery model with a poorly indexed sales table:
[Total Sales DQ] :=
SUM(Fact_Sales[Amount])
If the Server Timings show a single SE call lasting several seconds and scanning millions of rows, the issue is likely at the source: no predicate pushdown, missing partitioning, or lack of indexes. No amount of DAX rewriting will address this—your action here is to optimize the database or, in VertiPaq, to revisit model cardinality, partitioning, or encoding strategies.
Another red flag: if your model lacks a proper star schema (e.g., joins are many-to-many, or you have snowflake dimensions), the SE will work harder to resolve joins. DAX Studio will show expensive SE join operations. Correcting the data model—single-direction, one-to-many relationships from dimension to fact—often produces a step-change in SE performance, as the storage engine can then exploit its optimized join algorithms.
How to Read Server Timings Without Fooling Yourself
- High FE, many SE calls: DAX is forcing row-by-row evaluation. Check for iterators, context transition, or RELATED in the inner loop. Refactor to batch work into the SE.
- High SE, few calls: The storage engine is working on a large scan or join. Check model design, cardinality, and—if in DirectQuery—source database performance.
- FE “waits on SE” disguised as FE time: If lots of FE time appears, but most of it is spent waiting for SE calls, the call stack detail in Server Timings will show it. Don’t optimize DAX expressions before confirming whether the FE is actually doing work versus just brokering SE queries.
- DirectQuery traps: DirectQuery SE calls translate into SQL queries; poorly written DAX or missing data source indexes will surface as slow SE calls. But DirectQuery never benefits from VertiPaq caching—so expect higher SE times by design.
Don’t fall for averages: one miswritten measure can dominate the timings for an entire report page. Always profile the visual or measure in isolation before generalizing.
Checklist: Diagnosing and Acting on DAX Studio Findings
- Profile the slow visual or measure in DAX Studio with Server Timings ON.
- Scan the summary: is most time in FE or SE?
- Expand the call tree: many small SE calls (FE-driven iteration), or a few big SE calls (SE-driven scan/join)?
- Check rows scanned: high with low SE time isn’t a bottleneck; high with high SE time is.
- Refactor DAX to batch iterators into SE when FE is the bottleneck (e.g., by using ADDCOLUMNS, SUMMARIZE, or more explicit joins).
- Redesign the model or optimize the source database when SE is the bottleneck (e.g., indexing, star schema, partitioning).
- Re-test after each change to verify which engine now dominates—and stop when you reach acceptable performance, not “zero FE/SE time.”
Actionable next step: Next time you face a slow report, don’t guess. Run DAX Studio’s Server Timings, map the bottleneck to the right engine, and match your optimization effort to the real cause. Optimizing the wrong layer is wasted time—start with measurement, not hunches.