(+351) 21 24 10006  ·  info@bconcepts.pt
Carnaxide, Lisbon
Power BI: optimizing DirectQuery models for fast queries
Power BI

Power BI: optimizing DirectQuery models for fast queries

João Barros 18/11/2026 7 min

The exponential growth of data volumes and the need for near‑real‑time analytics have made DirectQuery a common choice for many Power BI projects. However, migrating to DirectQuery without a clear strategy can turn interactive reports into slow and frustrating experiences for end users, especially when sources are shared databases or transactional systems.

The issue is critical now: with teams demanding fresher insights and cloud architectures favoring centralized sources, the ability to deliver fast queries over DirectQuery has become an operational differentiator. This article explores concrete optimization techniques — from data modeling and query indexing to cache configuration and report design — to reduce latencies, control costs and maintain analytical integrity.

Understand the intent: when to choose DirectQuery instead of Import

The choice between Import and DirectQuery starts with understanding the analytical intent. DirectQuery is appropriate when you need minute‑level freshness, when the dataset is too large to fully load (for example you have hundreds of millions of rows) or when security/privacy rules require data to remain at the source. However, DirectQuery means each user interaction can trigger queries to the database, so performance depends on both the Power BI model and the source’s capacity.

Power BI: optimizar modelos DirectQuery para consultas rápidas

Consider these pragmatic metrics when deciding: acceptable latency (for example, under 2s per interaction), average number of concurrent users (20, 200, 2,000) and cost per query on the source infrastructure. If a dashboard with 50 simultaneous users generates 10 queries per user per minute, that’s 5000 queries/min — a volume that can lead to high costs and performance degradation without optimizations.

Efficient modeling: reduce the weight of DirectQuery queries

Correct modeling is the foundation of performance. In DirectQuery, simplifying relationships, minimizing columns and avoiding heavy calculated columns on the Power BI side reduces query complexity. Prefer physical and pre‑aggregated columns at the source. If you have fact tables with hundreds of millions of rows, create views or aggregated tables with appropriate granularity (day, store, category) for the most frequent queries.

For example, instead of allowing a product filter to trigger a query that performs joins across multiple dimension tables, expose a pre‑materialized view with the essential fields (date, store, product_id, gross_sales, units). Combining a compact analytical key with appropriate indexes at the source dramatically reduces response times.

Indexes, statistics and source tuning: what to ask from the DBA team

DirectQuery shifts performance responsibility to the database administrators. DBAs should create columnstore or B‑tree indexes according to query patterns, update statistics regularly and avoid unnecessary scans. A typical optimization for a reporting scenario is to create composite covering indexes on join and filter columns: for example (date, store_id, product_id). This reduces I/O and speeds aggregations.

It is also useful to enable the query plan cache and monitor execution plans to identify problematic queries. In cloud databases, consider autoscaling options for predictable peaks (Black Friday, launches) and associated costs. A recommended practice is to establish service level agreements (SLAs) between BI and DBA teams with concrete metrics: e.g., 95% of queries under 2s during peak hours.

Cache and aggregations in Power BI: balance freshness and latency

Even with DirectQuery, you can use caching and aggregations in Power BI to improve the experience. The query caching feature in Power BI Premium and the use of aggregated tables (Composite Models) allow frequent queries to be answered locally. Define cache refresh policies aligned with user needs: perhaps executive dashboards can have cache renewed every 5 minutes, while operational analyses require second‑level freshness.

Also use Partitional Incremental Refresh when compatible: although normally applied to Import modes, hybrid partitions can reduce the amount of data queried at the source. Another technique is to implement pre‑aggregates that Power BI can automatically target for group by queries or temporal hierarchies, reducing the need for massive scans on the base table.

Report design and DAX: reduce unnecessary queries

Many slow reports are not just caused by the source, but by poor design: excessive visuals, slicers with thousands of values and complex DAX measures that trigger sub‑queries. Simplify reports, limit the number of visuals per page to 6–8 and avoid slicers that show all SKUs for a dataset with 1M+ products. Use hierarchical filters (category→subcategory→product) to reduce the cardinality presented to the user.

In DAX, prefer simple functions and avoid unnecessary iterators. Measures that use FILTER on large tables become costly queries. Instead, compute pre‑aggregated values in the database or expose auxiliary columns that reduce the need for runtime calculations.

Mini practical case: national retail reducing latencies by 70%

Imagine a retail chain with 350 stores, 120 million annual transactions and DirectQuery dashboards that took 10–15 seconds per interaction. The team implemented a three‑phase strategy: (1) create aggregated views by day/store/product reducing active rows by 95%; (2) collaborate with DBAs to create composite indexes on date, store and product columns; (3) redesign dashboards to use 5 key visuals per page and enable query caching every 5 minutes for executive KPIs.

The result was an average latency reduction of 70% (down to 3–4s per interaction) and a 40% reduction in database compute costs during promotional peaks. The retail team was able to keep near real‑time data for operational decisions without sacrificing the user experience.

Actionable checklist to optimize DirectQuery today

  • Map the main queries: identify the 20% of queries that generate 80% of cost/latency.
  • Reduce cardinality: aggregate and expose pre‑aggregated views at the source.
  • Indexes and statistics: align with DBAs and schedule regular maintenance.
  • Configure cache and aggregations in Power BI (Composite Models) according to freshness SLAs.
  • Redesign reports: limit visuals, use hierarchies and progressive filters.
  • Monitor: establish metrics (p95 latency, cost per query, cache hit rate).

This list does not replace technical work, but serves as a roadmap to prioritize efforts and measure impact.

Conclusion: optimizing DirectQuery is a multifaceted effort

DirectQuery enables near real‑time analytics, but it is only effective when there is collaboration between analysts, data teams and DBAs, and when conscious decisions are made about trade‑offs between freshness and performance. The combination of efficient modeling, appropriate indexes at the source, intelligent use of cache and good report design results in measurable gains: 50–80% lower latencies and controlled operational costs in many real cases.

Start by measuring: how many queries per minute do your dashboards generate and what is the p95 latency? With that data, apply the checklist above in a one‑week pilot and quantify gains before scaling. Which DirectQuery report in your organization would be most valuable to optimize first?

← Back to insights
Let's talk?

Ready to transform your data?

Book a free 30-minute meeting and find out how we can help your team make better decisions.

Book a Free Meeting
bConcepts