DirectQuery in Power BI Can Be Fast — Here’s How to Design a High-Performance Semantic Model
DirectQuery (DQ) has a reputation for being slow—and for good reason. Unlike Import mode, it must query the database at runtime, which introduces unavoidable latency.
But here’s the good news: a well-designed semantic model can deliver fast, responsive experiences, even with DirectQuery.
This article outlines the key principles every Power BI professional should follow to ensure their DirectQuery models perform as close to Import mode as possible.
1. Understanding DirectQuery Performance Expectations
Even in optimal conditions, DirectQuery is inherently slower than Import because:
Data is not cached in VertiPaq
Every visual triggers SQL queries
Network, gateway, workloads, and concurrency impact performance
However, if your visuals consistently take more than 2–3 seconds to update after the first cached interaction, you likely have a design-level issue worth investigating.
How to quickly assess performance
A simple technique:
Change a slicer/filter.
Watch the spinner in the top-left of each visual.
Measure the time to refresh.
⚠️ Important: The Service caches results after the first run. To evaluate true performance, clear the cache or test with new filter combinations.
2. The Most Important Rule: Model Design Drives Performance
DirectQuery performance is 80% model design, 20% infrastructure.
Follow these non-negotiable rules:
2.1 Use a proper Star Schema
Avoid snowflakes, avoid many-to-many, avoid unnecessary bridges.
Star schema improves:
Query folding
SQL generation
Cardinality reduction
Storage engine execution paths
Bad schema = bad SQL = slow visuals.
2.2 Reduce the size of DirectQuery fact tables
Large fact tables directly increase query cost. Use:
Hybrid design (Import + DQ split)
Aggregation tables (Import) for summarized queries
Filtered views (e.g., latest 12 months)
Partitions (Fabric or Azure SQL)
2.3 Use Dual mode for all related dimension tables
Dimension tables should be:
Set to Dual mode (not Import)
This allows Power BI to:
Resolve relationships using in-memory dimensions
Avoid pushing unnecessary joins to the source database
Reduce SQL workload dramatically
This is one of the strongest performance optimizations available.
Deep dive reference: https://www.youtube.com/watch?v=TgAXt1ifdhs
2.4 Enable “Assume Referential Integrity” on relationships
When applicable, turn on Assume referential integrity.
This allows Power BI to generate INNER JOIN instead of OUTER JOIN, which:
Produces simpler SQL
Reduces execution time
Improves index usage
Be aware that if there are data in you factable that do not have a corresponding key in the dimension the specific data will not be shown in PowerBI
Deep dive Reference: https://www.sqlbi.com/articles/strong-and-weak-relationships-in-power-bi/
2.5 Cross-filter direction must be Single
Bi-directional filtering forces more joins and more complex SQL.
Best practice:
Fact → Dimension : Single
Avoid Bi-directional except in very controlled scenarios
Never use Bi-directional in large DirectQuery models
3. Use Import + DirectQuery Hybrid Fact Tables (“Dual Pipeline Design”)
The most effective pattern is to split your fact table:
Fact_Import: historical or less-frequently changing data
Fact_DQ: latest data only
Both connect to the same dimension tables.
Create a combined measure:
Measure_Total =
[Amount_Import] + [Amount_DQ]
Benefits:
Reduced DirectQuery volume
Faster visuals
Better user experience
Lower database load
This is the enterprise standard approach for “almost real-time” reporting.
How to: https://kb.accobat.com/kb/guide/en/import-and-direct-query-KRh0hWhiNs/Steps/3137021
4. DAX Measures: Keep Them Simple
Complex DAX can kill performance even in Import mode—so in DirectQuery it’s even more damaging.
Avoid:
❌ Nested iterators
❌ FILTER inside CALCULATE without indexed columns
❌ Complex row-level logic
❌ Many-to-many filter patterns
❌ Virtual tables unless necessary
Best practices:
Push transformations into the data source, not DAX
Keep measures thin and reference-based
Avoid DAX filtering that cannot fold to SQL
Test DAX with Performance Analyzer to identify bottlenecks
Remember: Only measures used in visuals affect performance.
5. Other Important Factors Affecting DirectQuery Performance
5.1 Number of concurrent users
More users = more queries = more load = more latency.
5.2 Visual complexity & count per report page
Guideline:
4–8 visuals per page max in DQ reports.
Avoid:
Card visuals with heavy DAX
Maps (very expensive)
5.4 Tile and dataset refresh schedules
Too-frequent refreshes invalidate cache → slower perceived performance.
6. Database / Storage Layer: Your Largest Bottleneck
DirectQuery pushes work downstream, so your SQL engine must be optimized.
6.1 Choose the right Azure SQL tier
For production DQ workloads:
Avoid S0–S3 (too slow, constrained I/O)
Minimum recommended: S4
Use Premium tiers for heavier loads
Why?
S4 and up use SSD storage and support higher IOPS, dramatically faster for DQ.
6.2 Scale strategy
Use scheduled scaling:
Scale up during business hours
Scale down afterward
Use autoscaling where available
This ensures performance while controlling cost.
6.3 Keep the SQL layer clean
Your DQ view should be:
Simple
Fully indexed
Without unnecessary functions
Limit triggers
Without scalar UDFs (very slow!)
Good SQL = Good DirectQuery performance.
Add Indexes on SQL tables that is used for Power BI DirectQuery
Table | Required Indexes |
Fact | Clustered (or columnstore), foreign key indexes, filter column indexes, sometimes composite |
Dimensions | Primary key index, attribute indexes used in slicers |
Date table | Index on DateKey / FullDateAlternateKey |
RLS tables | Index on the column defining security filter |
7. Power BI / Fabric Capacity Matters
Capacity affects:
Query processing limits
Cache behavior
Throughput
Isolation
Shared capacities are slower and unpredictable.
Premium/Fabric capacities allow:
Higher concurrency
Larger cache
Dedicated resources
If users complain about inconsistent performance, capacity is usually involved.
8. Power Query (Query Editor) — Keep It Minimal
When using DQ or Dual mode, Query Editor transformations are executed at runtime (unless folded), so:
Keep transformations to the absolute minimum
Ensure query folding all the way to the source
Avoid custom columns in Power Query when you can push logic to SQL
Always test folding using View Native Query.
9. In Summary: Yes, DirectQuery Can Perform—If You Design for It
DirectQuery problems almost always stem from:
Bad schema
Complex DAX
Poor SQL
Capacity limitations
Overly heavy report pages
But with:
A clean star schema
Hybrid Import + DQ fact tables
Dual mode dimensions
Simple DAX
Optimized SQL database
Proper scaling and capacity
…you can absolutely build high-performance DirectQuery models that feel almost as fast as Import.