Back to Blog

Troubleshooting DirectQuery Models in Power BI - What Actually Goes Wrong

October 5, 2026•9 min read•Michael Ridland

Every few months someone calls us with the same problem. They built a Power BI report on DirectQuery because the data was too big to import, or because the business wanted "real time", and now the report takes 40 seconds to load and the DBAs are angry about the queries hitting their production database. Sometimes visuals just fail with a timeout or a "resource limit exceeded" error and nobody knows why.

DirectQuery is the right choice in some situations. It's also the storage mode that generates the most support calls, because when it goes wrong the cause could be in Power BI, in the source database, in the network, in the gateway, or in the way the model was designed. Microsoft has a good article on troubleshooting DirectQuery models that covers the tooling. This post is about how we actually use that tooling on client engagements, and the problems we find most often.

First, figure out where the time is going

The biggest mistake I see is people guessing. They assume the database is slow and ask for a bigger server, or assume Power BI is slow and start rewriting DAX. Before you change anything, find out which layer is eating the time.

Performance Analyzer in Power BI Desktop is the first stop. Turn it on, refresh the visuals, and expand each visual's entry. You'll see the time split into DAX query, direct query, visual display, and "other". If "Direct query" is the big number, the time is being spent waiting on the source. If "DAX query" is large but "Direct query" is small, the formula engine is doing a lot of work locally, which usually points to a model or measure design issue. If "Other" is large, the visual was waiting in a queue behind other visuals, which often means too many visuals on the page.

That last one catches people out. A page with 25 visuals on DirectQuery fires off at least 25 queries, and Power BI limits how many it'll run in parallel against a single source. The rest wait. Cutting the page to 10 visuals can halve load time without touching anything else.

Copy the query out. Performance Analyzer lets you copy the DAX query, and you can paste it into DAX Studio or the DAX query view to see the SQL that Power BI generates. Run that SQL directly against the source. If it takes 30 seconds in SSMS, Power BI isn't the problem.

Tracing what Power BI sends to the source

For deeper digging, the Microsoft docs describe using SQL Server Profiler against the trace files Power BI Desktop writes locally. You enable tracing in Desktop's diagnostics options, and the trace files end up in your local AppData folder under the Power BI Desktop workspace directory. You can open them in Profiler and see each query, its duration and the SQL sent.

Honestly, most of our team uses DAX Studio for this now because it's friendlier, but the Profiler approach is still useful when you want to capture everything that happens during a full page load, including queries you didn't expect.

On the source side, get the DBA to run their own tracing. On SQL Server or Azure SQL that's Extended Events or Query Store. On Snowflake or Databricks it's the query history. What you're looking for:

  • Queries with huge scan volumes for small result sets
  • The same query running many times in a short window
  • Queries with odd shapes, like deeply nested subselects, that the optimiser struggles with
  • Queries running in sequence when you'd expect them in parallel

The DBA's view is often what breaks a stalemate. When the BI team and the database team are blaming each other, a query history showing that one slicer generates 14 separate queries tends to focus the conversation.

The problems we find most often

After doing this enough times, the root causes cluster into a handful of patterns.

Power Query transformations that don't fold

In DirectQuery, every transformation in Power Query has to translate into SQL for the source. If you've added steps that can't fold, Desktop will usually tell you it can't be used in DirectQuery mode. But some steps fold into horrible SQL. Merges, custom columns with complex logic and type conversions can produce queries that wrap your table in several layers of subselects. Each visual then runs that whole thing.

The fix is almost always to push the logic into a view or table in the source database. Power Query in a DirectQuery model should be close to "select from this view". If you're doing serious shaping in Power Query on a DirectQuery model, that's a design smell.

Calculated columns and complex measures

Calculated columns in DirectQuery are translated into SQL expressions that get evaluated on every query. Simple ones are fine. Ones using functions that don't translate well can cripple performance or get blocked entirely.

Measures are similar. Iterators like SUMX over a large table, or measures that use FILTER on a whole table instead of a column, can push the formula engine into pulling large intermediate results back from the source. You'll see this as "resource limit exceeded" errors, because Power BI caps intermediate results at one million rows by default. If you hit that, the query is trying to bring back too much data to calculate locally, and the measure needs rewriting.

Relationships on the wrong columns

DirectQuery relationships turn into joins. If those joins are on columns without indexes, or on text columns, or on columns with mismatched data types that need conversion, every visual pays the price. We've seen a single relationship on an unindexed varchar column add 15 seconds to every query on a page.

Also check whether "Assume referential integrity" is set on your relationships. When it's off, Power BI generates outer joins. When it's on and your data actually has referential integrity, it uses inner joins, which are usually much faster. Only turn it on if the data is clean, though. If there are orphaned rows, you'll silently lose them from results, which is worse than slow.

Bidirectional filtering

Bidirectional cross-filtering on DirectQuery models generates extra queries and more complex SQL. Sometimes it's needed. Often it was switched on to fix a slicer problem and nobody turned it off. Review every bidirectional relationship and ask whether it's really required.

Source database not designed for this workload

This is the uncomfortable one. A transactional database tuned for an ERP system handling individual record updates is not tuned for analytical queries aggregating millions of rows. If your DirectQuery source is the production OLTP database, you will have problems, and you may affect the ERP's performance too.

The answers here are architectural: a reporting replica, a proper data warehouse, columnstore indexes, or moving the data into a Fabric lakehouse or warehouse. Sometimes the right answer is to stop using DirectQuery altogether. Which brings me to the next point.

Ask whether you should be on DirectQuery at all

I'll be blunt: a lot of DirectQuery models we troubleshoot shouldn't be DirectQuery. The "real time" requirement often turns out to mean "updated a few times a day", which Import mode with scheduled refresh handles fine and with much better performance.

Options worth considering before you spend days tuning:

  • Import mode with incremental refresh for large fact tables. Handles a lot more data than people expect.
  • Composite models with dimension tables in Dual or Import mode and only the big fact table in DirectQuery. This alone often fixes slicer performance.
  • Aggregations so most queries hit an imported summary table and only drill-down queries go to the source.
  • Direct Lake if you're on Fabric and your data lives in OneLake. You get close to import performance without the refresh copy.

We've written before about when DirectQuery makes sense, and the short version is: it makes sense when the data genuinely can't be imported, or when freshness requirements are measured in minutes. Otherwise, Import or Direct Lake is usually the better call.

Gateway and network issues

If your source is on-premises, the on-premises data gateway is in the path for every query. A gateway on an undersized VM, or one sitting in a different data centre from the database, adds latency to every single query. We've seen a client in Perth whose gateway was running on a server in Sydney querying a database back in Perth. Each query made the round trip across the country twice.

Check the gateway's CPU and memory during peak use, put it close to the data source, and consider a gateway cluster if many reports depend on it. The gateway logs also show query durations, which helps separate gateway time from database time.

A troubleshooting checklist

When a client brings us a slow DirectQuery report, this is roughly the order we work through:

  1. Run Performance Analyzer and identify which visuals and which time category dominate.
  2. Count the visuals on the page. Cut anything that isn't essential.
  3. Copy the generated SQL and run it directly against the source.
  4. Get source-side query history from the DBA during a page load.
  5. Review Power Query steps for poor folding.
  6. Review calculated columns and measures for patterns that pull large intermediate results.
  7. Check relationships: join columns, indexes, data types, referential integrity setting, bidirectional filters.
  8. Check gateway placement and resource usage if on-premises.
  9. Ask honestly whether the model should be Import, composite or Direct Lake instead.

Most of the time, the fix shows up somewhere in steps 2 to 7. Occasionally it's step 9, and that's a bigger conversation.

Getting help

DirectQuery problems sit right on the boundary between BI development and database engineering, which is why they often fall through the cracks. The BI developer doesn't have access to source-side tracing, and the DBA doesn't know how Power BI generates queries. Getting both in the same room, looking at the same trace, is usually what cracks it.

If you're stuck with a slow DirectQuery model, our Power BI consultants can help diagnose it, and if the real answer is moving your data into a better platform, our Microsoft Fabric consultants work on exactly that kind of migration. Either way, measure first. Guessing is how teams end up paying for a bigger database server that doesn't fix anything.