Import Mode vs DirectQuery
Meera loves how fast the Weekly Sales report opens, but at 3 p.m. it still shows the numbers from the 7 a.m. refresh. Meanwhile the kitchen manager wants a screen of online orders placed in the last five minutes. Both requests trace back to a single choice made when you connect to a source: the storage mode.
The rule
Import copies the data for speed; DirectQuery asks the source every time.
Import. At each refresh, Power BI copies the data into the model and compresses it in a fast in-memory column store (the VertiPaq engine). Visuals read that copy, so they respond quickly, and every Power Query and DAX feature is available. The price: the data is only as fresh as the last refresh — up to 8 scheduled refreshes a day on Pro, 48 on PPU or a capacity — and the model counts against size limits, 1 GB per model on Pro.
DirectQuery. Only the table structure is stored. Each time someone opens a page, moves a slicer or changes a filter, Power BI turns the visual's request into a query and sends it to the source. The numbers are as current as the source, and the data can be far bigger than any model. The price: every click waits for the source, the source carries the load, some Power Query steps and DAX functions are restricted, and a single query may return at most about a million rows to Power BI.
| Import | DirectQuery | |
|---|---|---|
| Data stored in the model | Yes, compressed | No |
| Freshness | As of the last refresh | At the moment of the click |
| Speed | Fastest | As fast as the source |
| Size | Limited by the model size limit | Limited by the source |
| Features | Everything | Some restricted |
A table can later be switched from DirectQuery to Import, but a table set to Import cannot be switched back. Decide deliberately.
The shortcut
Default to Import. Move to DirectQuery only if one of two questions forces it: Must the data be fresher than a few refreshes a day allow? Is it too big to copy? If both answers are no, stay with Import.
Weekly Sales
Three years of till lines, about two million rows. Hourly freshness is not needed, and the data fits easily. Import.
The common mistake is choosing DirectQuery "to be safe" for a report that would be happy with Import. The report becomes slower for everyone, and the source database pays for every click.
Drills
Answer fast — trust the rule.
In which mode does the model hold a compressed copy of the data?
A user moves a slicer on a DirectQuery page. What happens?
Which need most clearly calls for DirectQuery?
Arjun set the Stores table to Import and now wants it in DirectQuery. What can he do?
An Import model on Pro must show fresh numbers at 7 a.m., 11 a.m., 3 p.m. and 7 p.m. Can scheduled refresh do that?
Remember
- Import copies the data for speed; DirectQuery asks the source every time.
- Default to Import; switch only when freshness or size forces you.
- Trap: DirectQuery "to be safe" makes every click slower, and setting a table to Import is one-way.