Real-Time Sounds Great Until the Report Opens

DirectQuery gets pitched as the answer to "we need live data," and in the right scenario it is. But I've watched more than one DirectQuery rollout turn a report that should open in two seconds into one that takes fifteen, and the cause is almost always a misunderstanding of what DirectQuery actually does on every single interaction.

What Happens Behind Each Visual

In Import mode, Power Query does its transformation work once, at refresh time, and - where the source supports it - folds those transformation steps back into a single native query against the source, so the heavy lifting happens in the source engine rather than in Power BI's own engine:

// Power Query step chain that folds back into one native query (Import mode)
let
    Source = Sql.Database("server", "database"),
    Orders = Source{[Schema="dbo", Item="Orders"]}[Data],
    Filtered = Table.SelectRows(Orders, each [OrderDate] >= #date(2024,1,1)),
    Grouped = Table.Group(Filtered, {"Region"}, {{"TotalAmount", each List.Sum([Amount]), type number}})
in
    Grouped

When this folds, Power Query never pulls Orders locally to filter and group it - the engine rewrites the whole step chain into a single query with a WHERE and GROUP BY sent to the source, and only the small result set comes back. Once that result lands in the model, every visual after that reads from an in-memory, columnar, compressed store. Query time and load time are cleanly separated.

DirectQuery collapses that separation entirely. There's no local cache to read from, so folding isn't really the relevant concept anymore - every visual, every slicer change, every cross-filter interaction sends a fresh native query straight to the source, waits for the result, and renders it. DirectQuery is the live query, every time, by design; there's no cached result sitting behind it to fall back on.

The Multiplication Problem

The part that catches people off guard is that it's not one query per report load - it's one or more queries per visual, and a busy dashboard page can easily contain fifteen to twenty visuals. A single card can be one query. A matrix with subtotals, a table with a few measures, and a chart with a legend can each generate multiple statements per render, and Power BI issues a batch of these concurrently, capped by connection limits, every time a slicer changes.

Stack a page with a date slicer, a region slicer, and a dozen visuals, and a single click can trigger dozens of round trips to the source, each one competing for the same connection pool and the same source-side query optimizer. If the source is also serving other workloads, or the underlying tables aren't indexed for the exact predicate shapes Power BI generates, that queuing compounds fast - and it compounds for every user viewing the report at once, because there's no shared cache to absorb repeated requests.

Where DirectQuery Genuinely Wins

None of this means DirectQuery is a mistake by default. It earns its place when:

  • The source data changes intraday and the business genuinely needs to see it within seconds - operational monitoring, live inventory, trading-style dashboards - not "would be nice to have," but a real, validated requirement.
  • The dataset is too large to import economically, and the source is a well-tuned engine designed for interactive query (a properly indexed data warehouse, a columnar MPP system) rather than a transactional database being asked to do double duty.
  • Row-level security has to reflect source-system permissions in real time rather than a periodically refreshed snapshot.

What it's not good for is dashboards that get refreshed hourly at most in the business's actual decision cycle, dashboards hitting a general-purpose transactional database that wasn't built for BI query patterns, or any report where "real-time" was requested as a reflex rather than an actual requirement anyone validated.

The Middle Ground Most Teams Skip

Between full Import and full DirectQuery sits composite modeling: keep the genuinely volatile fact table in DirectQuery, but bring reference and dimension data into Import (or Dual, so it behaves as either depending on the query), and - where the business tolerates a short lag - add an Import-mode aggregation table at a coarser grain that answers most visuals without ever touching the source. Most dashboards are 90% "show me last quarter by region" and 10% "show me what's happening right now." Composite models let each half run in the mode it actually needs, instead of forcing the entire report to pay DirectQuery's latency tax for questions that don't require it.

Diagnosing It With Performance Analyzer

When a DirectQuery report is slow, don't guess - measure. Open Performance Analyzer, start recording, and refresh the visuals on the page. For each visual you'll get three numbers: the DAX query time, the visual display time, and "Other." In a DirectQuery report, watch specifically for:

  • High DAX query time concentrated in a handful of visuals - that's the query round-trip to the source, and it's the number Import mode would mostly eliminate.
  • Multiple visuals all showing elevated query time simultaneously after one slicer click - that's connection-pool queuing, not any single visual being badly written.
  • The "Copy query" option on a slow visual, pasted into DAX Studio with server timings on, showing most of the time in storage engine (SE) calls rather than formula engine (FE) - that's the source doing the work, which means the fix is source-side (indexing, a materialized view, an aggregation table) rather than a DAX rewrite.
If Performance Analyzer shows every visual on a page spiking together right after a single filter change, that's not fifteen unrelated performance problems - it's one architectural problem. Fix the model, not the visuals.

The fix that's worked most reliably for me isn't tuning individual DAX measures - it's re-examining, table by table, which parts of the model actually need to be live, and moving everything else back to Import or Dual.