Import vs DirectQuery

Power BI · Getting Data

Theory

Every table has a storage mode. In Import mode, Power BI copies the data into its compressed in-memory engine (VertiPaq) inside the model; visuals are very fast and all DAX and Power Query features are available, but data is only as fresh as the last refresh. In DirectQuery mode, no rows are stored; each time a visual renders, Power BI sends a query to the source, so results are current but speed depends on the source, and some transformations and DAX functions are limited.

For example, a Sales table of a few million rows that changes nightly is ideal for Import with a daily refresh. A live order-tracking table of several billion rows in a cloud warehouse, where managers need figures from the last few minutes, is a candidate for DirectQuery. You choose the mode when connecting and can see it per table in Model view (Properties → Storage mode).

Why we use it

The storage mode is the biggest design decision for performance and freshness. Picking the wrong one leads to slow reports or stale numbers, and it is a favourite interview and PL-300 question.

When to use it

Default to Import: it is fastest and most flexible. Choose DirectQuery when data is too large to import, must be near real time, or must stay in the source for governance. Mixing both in one model is a composite model.

How it works

  1. Connecting to a source
  2. Is data too large to import or needed in near real time? Yes: Choose DirectQuery and optimise the source. No: Choose Import.
  3. Import chosen: will it refresh often enough? Yes: Set a scheduled refresh. No: Consider incremental refresh or DirectQuery.
  4. Check storage mode per table in Model view
  5. Model meets speed and freshness needs

Syntax

Connect → Data connectivity mode: Import | DirectQuery
Model view → select table → Properties → Advanced → Storage mode: Import | DirectQuery | Dual

Example

Decision for the sample model:
1. Customers and Products: small, change rarely → Import.
2. Sales history: a few million rows, updated nightly → Import with scheduled refresh.
3. A live 'OrderStatus' table updated every minute in the warehouse → DirectQuery.
4. Because the model now mixes modes, it is a composite model; set Customers and Products to Dual so they can join efficiently with both.

Key points

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic