Your Composite Model Is Slower Than DirectQuery Alone — Here's Why

A pattern keeps surfacing in Fabric community threads: a report loads in roughly two seconds on Power BI Desktop, then takes ten to twenty times longer once published to the Service. The model looks reasonable — a DirectQuery connection to a Tabular semantic model, a handful of imported SharePoint lists for reference data, and relationships linking them together. Nothing exotic.

The assumption is that imported tables are faster than DirectQuery tables, so mixing them in should help. It doesn't. Desktop masks the problem because it runs against a local Analysis Services instance with minimal network latency. The Service adds round-trip overhead between the front-end, the AS engine, and the remote DirectQuery source. Every cross-source join amplifies that overhead.

The real issue is architectural. A composite model containing both DirectQuery and Import tables forces the formula engine to act as an orchestrator between two storage engines. It cannot push a single native query to the source. Instead, it issues separate queries to each engine, pulls partial results into memory, and stitches them together. This serialization destroys the parallelism that makes either mode fast on its own.

Microsoft's own guidance is blunt: composite models that combine Import tables with DirectQuery tables introduce implications that aren't present in single-mode models. The documentation stops short of calling them slower, but the query plans don't lie. When you see a DAX measure that runs CALCULATE with REMOVEFILTERS across an imported dimension while the fact table sits in DirectQuery, you are watching the formula engine do manual labor that the storage engine should handle.

The specific DAX pattern that exposes this problem most brutally is a P&L-style measure using SWITCH over row index ranges. A typical implementation checks whether the current row is a detail line or a subtotal, then branches into different CALCULATE expressions — each applying REMOVEFILTERS on an index column and filtering to a different numeric range.

In a pure Import model, this pattern is tolerable. The storage engine resolves each branch against the VertiPaq columnar store in microseconds. In a composite model, each CALCULATE branch that touches a DirectQuery fact table generates a separate native query. A SWITCH statement with six branches can produce six round-trips to the remote source, each waiting for the previous one to complete if they share filter context dependencies.

The community thread that prompted this post showed exactly this: a measure computing Gross Profit, Total Overheads, Personnel Costs, EBITDA, and EBIT as separate SWITCH branches, each calling CALCULATE with different REMOVEFILTERS operations. On Desktop, the local AS engine handled this in under two seconds. In the Service, each branch triggered a remote query, and the cumulative latency made the report unusable.

Two specific DAX anti-patterns make this worse. First, using range predicates like [Index] > 7 && [Index] < 10 instead of explicit value lists with the IN operator. Range predicates prevent the storage engine from using hash lookups. Second, relying on limited relationships between imported and DirectQuery tables forces the formula engine to use TREATAS or internal crossjoins instead of native joins, adding another serialization step.

Replacing range conditions with [Index] IN {8, 9} lets the engine generate a more efficient WHERE clause. It's a small change with measurable impact — typically reducing per-branch query time by 15-30% in DirectQuery mode.

Understanding why consolidation outperforms DAX optimization requires knowing how the two Analysis Services engines divide work. The storage engine (SE) retrieves data from VertiPaq (Import) or the remote source (DirectQuery). The formula engine (FE) evaluates DAX logic — iterator functions, context transitions, SWITCH branches. In a single-mode model, the SE does most of the heavy lifting. In a composite model, the FE takes on orchestration duties it was never designed for.

When the FE encounters a measure that references columns from both an Import table and a DirectQuery table, it cannot delegate the entire computation to either SE. It must query each SE independently, materialize intermediate results in memory (a datacache), and then apply the remaining DAX logic. These datacaches are the hidden cost of composite models. They consume memory on the capacity, they serialize query execution, and they don't benefit from VertiPaq compression or DirectQuery pushdown.

This is why the most effective fix is almost always consolidation rather than DAX rewriting. Moving the SharePoint list data into the base Tabular semantic model — either by importing it there or by replicating it to the same SQL/Dataverse source — eliminates the cross-source boundary entirely. Every query can be resolved by a single SE, and the FE returns to its intended role of evaluating expressions rather than orchestrating data movement.

If consolidation isn't possible (permissions, data freshness requirements, organizational boundaries), the next best option is to restructure relationships. Using TREATAS with CALCULATETABLE to manually push filter context from imported dimensions to DirectQuery facts avoids the limited-relationship penalty. It's more verbose DAX, but it gives you explicit control over which values cross the source boundary.

Power BI Desktop's Performance Analyzer (View > Performance Analyzer) is the first tool to reach for, but its output is misleading in composite models if you read only the top-level duration. The total time reported for a visual includes both the DirectQuery duration and the formula engine's stitching overhead, but it doesn't break them apart clearly.

To isolate the cross-source cost, start a Performance Analyzer recording, interact with the report, then expand each visual's trace. Look for the "Direct Query" entry under each visual — this shows the time spent waiting for the remote source. Subtract this from the total visual duration. The remainder is formula engine overhead: context transitions, datacache materialization, and cross-source joins. In a well-optimized single-mode model, this remainder should be under 100ms. In a composite model with SWITCH-heavy measures, it regularly exceeds the DirectQuery time itself.

For deeper analysis, connect DAX Studio to the published dataset (if your capacity and permissions allow it). Run Server Timings on the problematic measure. The SE queries tab will show separate entries for VertiPaq and DirectQuery calls. Count them. If a single measure generates more than three SE queries, the SWITCH branching or cross-source joins are the likely cause.

DAX Studio also reveals another composite model penalty: query fusion failures. In pure Import mode, the SE can fuse multiple VertiPaq scans into a single batch. In composite mode, scans against different engines cannot fuse. Each stays independent, and the FE processes them sequentially. This is the mechanism behind the 'it was fast, now it's slow' experience that follows converting a model from Import to Composite.

Capture the Server Timings output before and after consolidating tables. The reduction in SE query count is the clearest proof that the fix worked.

Sometimes you cannot consolidate. The DirectQuery source is a published Tabular model owned by another team. The SharePoint lists update in real time and the business requires sub-hourly freshness. The organizational boundary is real. In these cases, defensive DAX and model design can limit the damage.

First, minimize the relationship surface between Import and DirectQuery tables. Every active relationship across the source boundary is a potential serialization point. If a SharePoint list serves only as a filter (not as a grouping dimension), consider removing the relationship entirely and using TREATAS inside measures to apply the filter. This gives you control over when the cross-source transfer happens.

Second, flatten SWITCH measures. Instead of a single measure with six CALCULATE branches, create six individual measures and use conditional visibility or a calculation group to display the right one. Each simple measure generates one SE query instead of forcing the FE to orchestrate six. The total query count may be similar, but the engine can parallelize independent measures across visuals.

Third, set the Maximum Connections per Data Source appropriately. The default is 10. If your report page has 15 visuals each generating two DirectQuery queries, 30 queries compete for 10 connection slots. Increase this to 20-30 on Premium/Fabric capacities where the source database can handle the concurrency. This doesn't reduce per-query time, but it reduces queuing delay.

Fourth, enable query reduction in report settings. Turn on the Apply button for slicers. Disable cross-highlighting between visuals that hit different storage engines. Each cross-filter interaction on a composite model can trigger a full cross-source reconciliation cycle.

MetricSign monitors Power BI dataset refresh duration and flags when a refresh that normally completes in two minutes starts taking eight. For composite models, this refresh_delayed signal often surfaces before users notice the Service-side performance degradation — giving you time to investigate the formula engine bottleneck before stakeholders start questioning the numbers on their Monday morning dashboard.

The instinct to add a small imported table to a DirectQuery model is understandable. SharePoint lists, Excel reference tables, and manual mapping files are the connective tissue of enterprise reporting. But each one adds a cross-source boundary that the formula engine must manage at query time.

Before adding an Import table to a DirectQuery model, run this test: create the measure in a pure DirectQuery model with the reference data loaded into the same source. Measure the query time in DAX Studio. Then create the composite version. Compare SE query counts and total duration. If the composite version generates more than double the SE queries, the cross-source overhead will compound as the model grows.

The community discussion that inspired this post ended with the original poster consolidating their SharePoint tables into the base Tabular model. Report load time in the Service dropped from over fifteen seconds back to under three. No DAX was rewritten. No measures were restructured. The only change was eliminating the source boundary.

This matches a broader pattern. Import mode with full VertiPaq compression remains the fastest storage mode for analytical queries. DirectQuery with proper indexing and Assume Referential Integrity handles real-time requirements efficiently. Composite models serve a genuine purpose when aggregation tables accelerate high-grain DirectQuery queries. But using them as a convenience layer to avoid moving reference data into the proper source almost always backfires once the report reaches the Service and real network latency enters the equation.

The diagnostic sequence is straightforward: Performance Analyzer to identify the slow visual, DAX Studio Server Timings to count SE queries, and a consolidation test to confirm the fix. Most teams complete this in under a day.