What it does

DirectQuery keeps the data in the source and stores only model metadata. Every visual interaction turns into native queries sent to the source at query time, so report users always see current data and the model itself stays tiny.

Key facts

• Power Query steps and DAX expressions are limited to what can be translated into native queries the source understands. Calculated tables aren't supported.
• Intermediate results are capped at one million rows. Exceed it and the query fails rather than degrading.
• Dashboard tiles built on DirectQuery models can update as often as every 15 minutes, and automatic page refresh is available for near real-time report pages.
• Maximum Connections per Data Source controls how many queries Power BI sends to the source in parallel. Raising it pushes more load onto the source, which doesn't automatically make anything faster.
• Assume referential integrity makes Power BI generate INNER JOIN instead of OUTER JOIN. Faster, but it silently drops unmatched fact rows if integrity is broken.
• Median isn't pushed down — Power BI pulls detail rows back and calculates it locally, which is where the one-million-row limit tends to bite.
• TopN filters retrieve all values from the source first and rank afterwards, so high-cardinality columns are expensive.
• Relationships on GUID columns generate a cast in the join and perform badly. Materialise a different data type in the source.

When to use / skip

Use DirectQuery when the data genuinely can't be imported — volumes that won't compress into capacity even after reduction, or a freshness requirement that scheduled refresh can't meet. Use it when security must be enforced by the source and can't be reproduced as row-level security. Otherwise don't. Microsoft's own guidance targets visuals refreshing in five seconds or less, and hitting that against a shared transactional database usually means indexes, materialised views and a DBA on the project team. A successful DirectQuery delivery is a joint effort with the database owners, not a modelling decision you make alone.

Configuration decisions

• Whether the source is a purpose-built reporting layer or the live transactional system — this decides whether the project is feasible at all.
• Which tables stay DirectQuery and which move to Import or Dual, i.e. whether you're really building a composite model.
• Whether Assume Referential Integrity is safe to enable, table by table, based on actual data quality rather than the schema's foreign keys.
• Maximum connections per source, balanced against what the source can take at peak concurrency.
• Which query reduction options are on: apply buttons on slicers and filters, cross-highlighting off by default.

Gotchas

• Relative date filters in Power Query translate into hard-coded date literals in the native query. Put relative-period columns in the date table instead.
• Relationships built on calculated columns embed the expression into every source query and usually defeat indexes. Materialise the column upstream.
• Bi-directional filtering generates joins that perform badly under DirectQuery. Use it only where a bridging table genuinely needs it.
• Table and matrix totals often need extra round trips, and always do for distinct count, median, and anything over SAP HANA or SAP BW. Switch totals off where they aren't needed.
• Multi-select slicers fire a fresh query on every click unless you show an Apply button.
• Report authors won't know any of this. The performance problem will be attributed to Power BI, not to the report design.

Consultant notes

• Get the DBA into the project from the start. Most of the wins are indexes, materialised transformations and a date table in the source, not anything you can do in Desktop.
• Set expectations with report consumers explicitly: slower interactions, a Refresh button that matters, and the possibility of inconsistent results across visuals on a volatile source. Users who understand why are far more forgiving.
• Prototype with real data volumes and real concurrency before committing. A DirectQuery model that's fine for the architect on a quiet afternoon can fall over at month-end.
• Have the composite model conversation as the exit route. Aggregation tables over DirectQuery facts recover most of the performance without abandoning the design.
• Bear in mind Microsoft is consolidating capacity purchasing onto Fabric F SKUs and retiring the Power BI Premium per-capacity SKUs — check current licensing guidance before quoting anything about capacity throughput limits.

---

Worth revisiting after any significant change to the source platform, or if the one-million-row intermediate limit ever moves.