A team needs current data, reads that DirectQuery queries the source live, picks it, and six weeks later has a report that takes fifteen seconds to load and a database administrator asking why the warehouse is being hammered all day.
DirectQuery is the right answer sometimes. It is picked for the wrong reason far more often than it is picked wrongly, and the difference matters.
The Wrong Reason to Pick DirectQuery
The reason is nearly always “we need real-time data”. The question nobody asks is what decision depends on the data being current to the second rather than to the hour.
For most reporting the honest answer is none. A finance dashboard consumed each morning does not need live queries. An executive summary reviewed weekly certainly does not. Picking DirectQuery for these costs you query performance on every interaction, permanently, to solve a problem you did not have.
What Actually Changes Between Them
| Import | DirectQuery | |
|---|---|---|
| Where data lives | Compressed in the model | Stays in the source |
| Query speed | Fast — in-memory columnar | Depends entirely on the source |
| Data freshness | As fresh as the last refresh | Live at query time |
| Load on source | Only during refresh | Every interaction, all day |
| Model size limits | Bounded by capacity memory | Effectively unbounded |
| DAX available | Everything | A meaningful subset |
| Complex measures | Fine | Often slow or unsupported |
That last pair is the one people discover late. DirectQuery translates DAX into the source query language, and not everything translates. Time intelligence, complex filter context and some iterators either run poorly or are unavailable — which is usually found out after the model is built.
The Four Questions That Decide
What decision needs data fresher than an hour?
If you cannot name one, use Import. This eliminates most cases before any technical discussion.
Where you can name one — live inventory during trading, production line state, fraud signals — that is a genuine DirectQuery case, and usually only for the tables involved in that decision.
Does the data fit in memory?
Import compresses aggressively, often ten to one or better on well-shaped data. A table that sounds enormous frequently fits comfortably.
Where it genuinely does not — several billion rows of telemetry — DirectQuery is the answer regardless of freshness requirements.
Is the source fast enough to serve interactive queries?
This is the question that sinks most DirectQuery implementations. Every slicer click becomes a query against your source. If that source is a transactional database already under load, you are adding reporting traffic to a system doing real work.
Test before committing: run your five most complex queries directly against the source at a busy time of day. If any takes more than a couple of seconds there, it will be slower through Power BI.
How complex is the DAX?
Sophisticated time intelligence, many-to-many relationships and nested filter contexts all behave worse under DirectQuery, and some patterns simply do not translate.
If your reporting depends on that kind of logic, Import removes an entire category of problem you would otherwise spend the project working around.
Composite Models Are Usually the Answer
The framing as a binary choice is the underlying mistake. Composite models let you set storage mode per table, which maps far better onto how businesses actually work.
A realistic composite setup
Sales (last 90 days) DirectQuery live, small, high value
Sales (history) Import large, static, fast
Date Dual serves both sides
Customer Dual serves both sides
Product Import small, changes rarely
Inventory (live) DirectQuery the actual real-time need
Result: current-day figures are live, historical
analysis is fast, and dimensions do not force a
round trip to the source on every query.Dual storage mode on dimensions is the part that makes this work. A dimension marked Dual can serve Import queries from memory and DirectQuery queries from the source, so joining a live fact table to a date dimension does not force the whole query to the source.
If You Already Picked Wrong
Moving from DirectQuery to Import is usually straightforward: change the storage mode, set up refresh, and test the DAX that may have been written around DirectQuery limitations. Budget time for the last part; there is often more of it than expected.
Moving the other way is harder, because Import models are frequently built with assumptions that do not hold under DirectQuery — calculated columns, complex measures, tables too large to query interactively.
The pragmatic middle path is usually to convert to a composite model rather than flipping wholesale. Identify the tables that genuinely need to be live, set those to DirectQuery, leave everything else Imported.
One more practical note on testing. Whatever you pick, test it with the number of concurrent users you actually expect, not with one person clicking. DirectQuery performance degrades with concurrency in a way Import does not, because every user is generating source queries rather than reading from a shared in-memory model. A report that feels acceptable in development can become unusable the morning forty people open it at once.
A Decision Table You Can Use Today
If you want the short version, find the row that matches your situation:
| Your situation | Storage mode | Why |
|---|---|---|
| Monthly finance reporting | Import | Nothing about the decision needs sub-daily data |
| Executive weekly summary | Import | Freshness requirement is measured in days |
| Live inventory during trading | Composite | DirectQuery on stock only, Import for everything else |
| Production line monitoring | Composite | Live on the current shift, Import on history |
| Three billion rows of telemetry | DirectQuery | Will not fit in memory at any capacity tier |
| Heavy time intelligence | Import | Translation to the source is where DirectQuery struggles |
| Source is a busy transactional DB | Import | Do not add reporting load to a system doing real work |
| Regulatory need for no data copy | DirectQuery | A genuine constraint, and one of the few absolute ones |
The pattern across that table is that the honest DirectQuery cases are narrow and specific. Everything else is Import, or Import with a small live slice — which is what a composite model is.
Three Things That Catch People Out
Row-level security behaves differently. Under DirectQuery, RLS filters are pushed into the source query, which means your security logic has to be expressible in the source system and performs at the source system's speed. A dynamic RLS pattern that resolves instantly against an imported model can add seconds per query against a busy warehouse.
Refresh does not mean what you think. A DirectQuery dataset still has a refresh schedule, but it is refreshing metadata and any imported tables, not the DirectQuery data. Teams sometimes conclude their live data is stale and go looking for a refresh problem that does not exist.
The source connection is a single point of failure during business hours. An Import model whose source is down still serves yesterday's data perfectly. A DirectQuery model shows an error. That is a genuine availability trade-off and it belongs in the decision, not discovered during an outage.
What We Would Do
We start from the decision rather than the preference. Which decisions depend on live data, how fast the source answers under real load, and what the DAX needs to do. Storage mode falls out of those answers rather than being chosen up front.
In practice most environments we review end up composite: a small live slice where the decision genuinely needs it, and everything else Imported for speed.
If your reports are slow and you are on DirectQuery, storage mode is the first thing worth checking. See data modeling, or performance optimization if the symptom is speed rather than correctness.