Power BI How-To / Architecture
Power BI DirectQuery vs Import Mode: How to Choose the Right Storage Mode
The power bi directquery vs import mode decision comes down to one trade: Import copies data into Power BI’s in-memory engine for the fastest visuals, while DirectQuery leaves data at the source and queries it live for maximum freshness. This guide compares the two mode by mode, shows when each wins, and covers composite models that mix both.
What is the difference between Import and DirectQuery?
Import mode copies data into Power BI’s compressed in-memory engine (VertiPaq) at refresh time, so every visual reads from that fast local copy. DirectQuery stores nothing: each visual translates into a query sent to the source database at view time, so results are as fresh as the source but only as fast as it answers.
That single architectural difference drives everything else — speed, freshness, size limits, and which DAX and Power Query features are available. Import is Power BI’s default and the mode most reports should use; DirectQuery exists for the cases where copying the data is impossible or staleness is unacceptable. Microsoft’s DirectQuery documentation (opens in new tab) details the current feature differences and source support; the table below summarizes the decision.
| Factor | Import mode | DirectQuery mode |
|---|---|---|
| Where data lives | Copied into Power BI’s in-memory engine | Stays in the source; queried live per visual |
| Visual performance | Fastest — reads from compressed local memory | Depends entirely on source speed and query tuning |
| Data freshness | As of the last refresh | Near real-time — every view hits current data |
| Dataset size | Bounded by license/capacity memory limits | Effectively unbounded — data never leaves the source |
| DAX and Power Query | Full feature surface | Restricted — some functions and transforms unsupported or slow |
| Load on the source | Only at refresh time | Continuous — every report viewer generates queries |
| Typical fit | Most business reporting and dashboards | Operational monitoring, huge sources, strict data-residency rules |
When does Import mode win?
Import wins whenever the data fits in memory and a scheduled refresh is fresh enough — which describes most finance, sales, and operations reporting. It gives the fastest visuals, the full DAX and Power Query feature surface, and no ongoing query load on the source system while people browse the report.
Monthly and daily reporting rhythms are Import’s home turf: a dataset refreshed overnight is exactly as current as the business process it reports on. Compression is a quiet superpower here — VertiPaq routinely shrinks source data dramatically, so models hold far more history than teams expect. The default advice is blunt: use Import unless a specific, named constraint forces you off it, because most ‘we need real-time’ requirements turn out to mean ‘refreshed this morning.’
When does DirectQuery win?
DirectQuery wins when the data is too large to copy, when policy forbids data leaving the source, or when the report genuinely needs near real-time numbers — operational dashboards watching today’s orders or ticket queues. It trades visual speed and some DAX capability for zero-copy freshness.
Those trades are real. Every interaction on a DirectQuery report fires queries at the source, so a slow warehouse means a slow report, and a popular report means real load on the database — a busy report page can multiply into many queries per viewer. DirectQuery reports reward disciplined design: fewer visuals per page, pre-aggregated views on the source side, and indexes matched to the report’s filters. If a DirectQuery report is crawling, the fix usually lives in the source or the model, not the visuals — our guide to fixing slow Power BI reports covers the diagnosis order. For a build on a relational source, see SQL Server Power BI dashboard development.
What are composite models, and when should you mix modes?
A composite model mixes storage modes in one dataset: some tables Import, others DirectQuery, set per table. The common pattern keeps big fact tables in DirectQuery for freshness or size while small dimension tables Import for fast slicers — with dual mode letting a table serve both sides efficiently.
Composite models also power aggregations: a small imported summary table answers most visuals instantly, and Power BI transparently falls through to the DirectQuery detail only when someone drills below the summary grain. This is the standard escape hatch when a source is too big to import whole but too slow to query raw. It adds genuine modeling complexity — relationship behavior across modes has rules of its own — so treat it as a deliberate architecture step, not a checkbox. On Microsoft Fabric, Direct Lake offers a related third path for lakehouse data; our Microsoft Fabric consulting page covers when that architecture fits.
How should a team actually decide — and when is it worth getting help?
Decide by constraint, not preference: start from Import; move a table to DirectQuery only for a named reason — size beyond capacity, data-residency policy, or a true real-time requirement; reach for a composite model when only some tables carry that constraint. Write the reason down next to the choice.
The expensive mistakes are usually mode choices made early and never revisited: an all-DirectQuery model built ‘to be safe’ that crawls forever, or an Import model that outgrew its capacity and now fails refresh at month-end. Both are fixable — storage mode is a per-table setting, not a life sentence. Re-architecting a model across modes touches relationships, measures, and refresh design at once, which is exactly the scoped, senior work our data modeling and DAX service handles: we quote the hours first, and you approve before work begins.
Not sure which storage mode your model should use?
Storage-mode decisions ripple into performance, refresh, and DAX for years — and they are cheapest to get right early. Bring your source list to a free demo: we scope the architecture call, you approve the hours, and senior prepaid hours cover the work.
FAQ
Frequently asked questions
For visual response time, almost always — Import reads from a compressed in-memory copy, while DirectQuery waits on the source database for every interaction. A well-tuned warehouse can make DirectQuery feel acceptable, but Import is the performance ceiling. Choose DirectQuery for its freshness or size advantages, never for speed.
Yes — storage mode is set per table, and moving from DirectQuery to Import (or to dual) is supported. Going from Import back to DirectQuery is more restricted and often means rebuilding the table connection. Because switching can affect relationships and measures, treat a mode change as a small modeling task with testing, not a toggle.
Exactly as fresh as the last successful refresh. With scheduled refresh running daily or several times a day, an Import report is typically current to within hours, which satisfies most business reporting. If stakeholders genuinely need minutes-old numbers, that is the named constraint that justifies DirectQuery or a composite design.
Composite models are broadly available in Power BI Desktop and the service, but specific capabilities — notably aggregations behavior and capacity-dependent limits — vary by license tier, and Microsoft adjusts these over time. Check the current licensing documentation for your tier before committing an architecture to them.
Direct Lake is a Fabric-specific mode that reads Delta tables from a lakehouse directly into the VertiPaq engine, aiming for Import-like speed without a copy step. It applies only when your data already lives in Fabric OneLake. For teams on that platform it can replace the Import-versus-DirectQuery trade for lakehouse data entirely.
Try us risk-free for 4 hours
If you’re unhappy within the first 4 hours of your block, we’ll refund you in full — no questions asked. Hours are deducted only for work you’ve reviewed and approved, and they never expire.
Book a free demo
Get senior Power BI help, on demand
Tell us the task. We scope it, you approve, and a senior consultant delivers — typically in 3–5 business days. Prepaid hours that never expire.
- Prepaid hours from $100/hour — and they never expire
- Senior-led delivery — 20+ years on the Microsoft platform
- Typical 3–5 business day turnaround
- Full refund if you’re unhappy in your first 4 hours
The fastest way to reach us is the form — tell us the task and we'll reply within one business day.