DirectQuery

What is DirectQuery?

DirectQuery is a storage mode for Power BI models in which the data never lives inside the model itself. Every time a user opens a report or changes a filter, Power BI translates the visual into a query in the source's language (typically SQL) and sends it live to the database. The result comes back and renders in the visual.

The alternative is called Import: during a refresh Power BI copies the data into its in-memory engine (VertiPaq) and keeps it there. Queries then run against that fast, compressed copy.

Picture DirectQuery as a live interview with an expert: every answer is fresh, but you depend on how quickly that expert responds. Import is like a book you wrote in advance with answers to the most common questions: lightning-fast lookups, but only as current as the last edition.

When do you choose DirectQuery?

1. Near real-time data needed. Production lines, inventory, trading positions, SLA dashboards. When data older than ten minutes is already too old, Import is off the table.

2. Very large datasets. Tables with billions of rows that do not fit in a Power BI capacity's memory. DirectQuery leaves the data at the source and only fetches what each visual needs.

3. Strict governance at the source. When the source database is the single source of truth and no copies are allowed, DirectQuery keeps everything in place.

4. Frequently changing data. A refresh every 30 minutes costs more than DirectQuery when users only check a handful of times per day.

5. Regulation and audit. Some sectors require reports to run live on the source, with every query logged at the source.

DirectQuery versus Import

The most frequent question when building a new Power BI model.

Performance

Import almost always wins. The VertiPaq engine is tuned for analytical queries on in-memory columnar data and often delivers sub-second responses on hundreds of millions of rows. DirectQuery relies on the source, and the source is rarely as fast as VertiPaq. For classic BI dashboards, Import is the default.

Freshness

DirectQuery wins. You see the data as it currently exists in the source, without waiting for a refresh.

Functionality

Import supports everything Power BI can do: all DAX functions, all visuals, full time intelligence. DirectQuery has limitations: not every DAX function works, Quick Insights is disabled, and visual rendering is only as quick as the source.

Scale

DirectQuery wins on extreme volumes. Import is bounded by your Power BI capacity memory (usually a few GB compressed). DirectQuery has no such ceiling.

Cost

Import consumes Power BI capacity during refresh. DirectQuery uses no refresh capacity, but continuously loads the source database. Every user interaction is another round trip.

Rule of thumb

Pick Import unless you have an explicit reason to use DirectQuery. Very large datasets, very fresh data, or governance that forbids copies. In all other cases Import simply delivers faster reports.

Composite models: the best of both

Since Power BI introduced composite models, you can mix tables in different modes within a single model.

A fact table in DirectQuery (billions of transactions) linked to dimension tables in Dual mode (customers, products, dates). Dual mode lets the engine pick, per query, whether to use the Import copy or the DirectQuery version.

Composite models are the norm today for enterprise scenarios that need live data plus fast interactivity. They also show up in Microsoft Fabric, where a semantic model can seamlessly mix lakehouse tables (DirectLake) with imported data.

DirectLake as a new option in Fabric

Fabric introduced a third mode: DirectLake. It reads Delta tables straight from OneLake as if it were Import data, skipping the refresh step. Performance feels like Import without the historical delay and without the refresh schedule limitation. For Fabric-native reporting, DirectLake is often the best choice, with DirectQuery left as a fallback for sources outside OneLake.

Pitfalls of DirectQuery

A source that does not scale
A Power BI report with a thousand users generates a thousand times more queries against your operational database. Without a dedicated read replica or cube layer, you can bring the OLTP system down.

Query folding that breaks
DirectQuery relies on Power BI being able to translate every interaction into the source. Some transformations in Power Query break that translation. Avoid custom M functions and exotic data types.

Slow visuals
A page with ten visuals can fire ten queries on every filter change. Use Apply filter buttons, limit visuals per page, and configure a query reduction strategy.

Limited DAX set
Some time intelligence, iterator functions, and more complex patterns do not work or work poorly in DirectQuery. Check early which measures you need and whether they are supported.

Source must always be online
An Import report keeps working while the source is down. DirectQuery simply stops. Plan for maintenance windows and failover.