Every Power BI semantic model starts with a storage mode decision, and that decision shapes everything downstream: query speed, DAX flexibility, refresh schedules, licensing costs, and how much pressure your source systems absorb during business hours. Import mode, DirectQuery, and composite models each solve different problems, and choosing the wrong one creates pain that compounds as the model grows. This guide breaks down the performance characteristics, compatibility limits, and practical trade-offs so you can match the right storage architecture to your workload.
For a broader look at how Power BI connects to source systems before data reaches the model layer, see How Power BI Connects to Your Data.
Table of Contents
How Power BI Import Mode Stores and Queries Data
Import mode copies data from the source into the VertiPaq engine, a columnar, in-memory store built for analytical queries. Once the data lands in VertiPaq, every report interaction runs against the local cache. No round-trips to the source system happen at query time.
The compression is aggressive. VertiPaq uses dictionary encoding, run-length encoding, and value encoding to shrink datasets to roughly one-tenth of their original size. A 10 GB source table typically compresses to about 1 GB inside the model, sometimes less if the cardinality is low. That compressed footprint matters because it determines whether your model fits within your license tier’s size limit.
Query execution is fast because everything stays in memory. Sub-second response times are normal for well-modeled Import datasets. Calculated columns, calculated tables, complex DAX iterators like SUMX and FILTER, time intelligence functions, many-to-many relationships, and bi-directional filtering all work without restriction. If a DAX pattern exists, Import mode supports it.
The cost is freshness. Import mode loads a snapshot. Between refreshes, the data is static. Pro licenses allow eight scheduled refreshes per day. Premium Per User and Premium capacity bump that to 48, with additional XMLA endpoint refreshes that do not count against the limit. For most reporting scenarios, hourly or even daily refreshes are fine. For operational dashboards that need minute-level freshness, Import alone is not enough.
Import Dataset Size Limits by Power BI License Tier
The compressed model size, not the raw source size, determines whether your data fits. Here is where the limits land across license tiers:
Pro (per-user license): The ceiling is 1 GB per dataset. Since VertiPaq compresses at roughly 10:1, that typically covers 5 to 10 GB of source data. Workspace storage caps at 10 GB total. There is no XMLA write support and no large dataset format option.
Premium Per User (PPU): The default limit rises to 10 GB, expandable to 100 GB with large dataset storage format enabled. XMLA endpoints are available, and incremental refresh policies can manage partitions automatically.
Premium capacity and Fabric F SKUs: The default is also 10 GB, but large dataset format pushes the ceiling to the capacity’s available memory. A P1 capacity offers roughly 25 GB per model. Fabric F SKUs scale based on the provisioned capacity tier, with total workspace storage reaching up to 100 TB.
One detail that catches teams off guard: Power BI Desktop enforces a 10 GB upload limit regardless of your service license. Models grow beyond 10 GB only through refreshes inside the Power BI service, not through direct publish from Desktop.
The differences between Pro, PPU, and capacity licensing go well beyond storage. For a full pricing and feature breakdown, see Power BI Pro vs Premium and Fabric in 2026.
DirectQuery: Live Queries Against the Source System
DirectQuery takes the opposite approach from Import. No data is stored in the Power BI model. Only metadata lives there: table names, column definitions, relationships. Every time a user clicks a slicer, expands a matrix, or loads a dashboard tile, Power BI translates the visual’s requirements into one or more native queries and sends them to the source.
That architecture has one clear strength: the data is always current. There is no snapshot, no refresh lag, no partition strategy to maintain. Whatever the source contains at the moment the user interacts with the report is what they see.
The cost is latency. Typical visual load times range from 2 to 30 seconds depending on the source engine, query complexity, indexing, and network distance. Microsoft’s own guidance sets a target of under 5 seconds for common visuals. In benchmarks comparing identical datasets, Import mode returned visuals in 1 to 4 seconds while DirectQuery took 15 to 89 seconds on the same queries. A 4-minute hard timeout exists in the Power BI service. Any query that exceeds it fails outright.
Source system load is the other concern. Each visual on a report page fires at least one query. A dashboard with 12 visuals viewed by 50 concurrent users generates hundreds of queries per minute. Row-level security makes it worse, because each user’s tile refresh runs as a separate query with distinct filters. For teams running DirectQuery against a transactional database that also serves the application, the additional analytical load can degrade both the reports and the production system.
Query reduction settings help. You can add an Apply button to slicers so queries fire only when the user explicitly commits a filter change. Disabling cross-highlighting reduces the cascade of secondary queries. Reducing the number of visuals per page is the most effective lever, but it requires report design discipline.
Microsoft documents the full set of DirectQuery performance tuning options in their official DirectQuery guidance.
DAX Limitations When Using DirectQuery Storage
Full DAX support is one of Import mode’s biggest advantages, and the gaps in DirectQuery are real. Calculated columns in DirectQuery tables can reference only columns from the same table, and the expressions must fold to the source’s native query language. Calculated tables are not available at all in pure DirectQuery models (in composite models, they are created as Import tables). Time intelligence functions like DATEADD and DATESYTD are restricted. Quick Measures are unavailable. The FORMAT function cannot be used in calculated columns. Auto date hierarchies do not generate, so you must build an explicit date table.
Complex iterators like SUMX and FILTER technically work, but each iteration generates queries against the source. A SUMX iterating over 50,000 rows does not loop in memory; it can trigger thousands of source queries, pushing response times from seconds into minutes. The DAX is “supported” in the sense that it executes, but the performance can make it unusable in practice.
These restrictions shape the modeling approach from the start. If your report design relies heavily on calculated columns, time intelligence, or complex row-level iterators, DirectQuery on its own will force either significant DAX rewrites or unacceptable performance. That gap is exactly where composite models become relevant.
Composite Models: Combining Import and DirectQuery in One Semantic Model
A composite model contains tables in more than one storage mode within a single .pbix file. The most common pattern puts large fact tables in DirectQuery (to avoid importing billions of rows) while keeping smaller dimension tables in Import (for fast filtering and full DAX compatibility).
The key to making this work cleanly is Dual storage mode. A table set to Dual can behave as either Import or DirectQuery depending on the query context. When a report visual touches only Dual and Import tables, the query resolves entirely from the in-memory cache. When the same Dual table joins with a DirectQuery table, it switches to DirectQuery and the query goes to the source.
Why does this matter? Without Dual mode, a join between an Import dimension and a DirectQuery fact creates what Power BI calls a “limited relationship.” Limited relationships are slow. The join cannot be pushed to the source, so Power BI pulls data from both sides and executes the join locally on unindexed data. Dual mode avoids this by ensuring that dimension tables can participate in source-side joins when needed.
Power BI actually nudges you toward this pattern. When you change a table from DirectQuery to Import in a composite model, the editor suggests setting related dimension tables to Dual automatically.
There is a subtlety with Dual tables worth knowing: their cache can go stale. A Dual table’s Import-side cache refreshes on the normal schedule, but when it acts as DirectQuery, it pulls live data. That means a single visual on a page might show cached dimension values alongside real-time fact data. In practice, this rarely causes problems for daily-refresh models, but it can create confusing discrepancies when data changes frequently. SQL Server Profiler or DAX Studio can help: VertipaqSE Query Begin events indicate a cache hit, while DirectQuery Begin events show source queries.
User-Defined Aggregations for Large Power BI Datasets
Composite models become especially powerful when combined with user-defined aggregations. The idea is straightforward: you create a pre-aggregated Import table that covers the most common query patterns, and Power BI transparently routes matching queries to that fast, in-memory table instead of hitting the DirectQuery source.
Users never see the aggregation table. They query the detail table as usual. Behind the scenes, Power BI checks whether the requested granularity and measures match what the aggregation table can answer. If the query asks for total sales by month and product category, and the aggregation table stores exactly that grain, the response comes from cache in milliseconds. If the query drills down to individual transaction rows, it falls through to the DirectQuery detail table.
Setting this up requires a few deliberate steps. First, build the aggregation table as an Import table, either through a pre-aggregated view at the source or as a Power Query transformation. Then use the Manage Aggregations dialog to map each column: set the summarization type (Sum, Min, Max, Count, GroupBy), point it to the matching detail table and column, and assign precedence if multiple aggregation tables exist. Power BI auto-hides the aggregation table from report authors after you apply the configuration.
The hit-or-miss logic follows clear rules. An aggregation hit occurs when the query’s granularity matches or is coarser than the aggregation grain, and all GroupBy columns in the query are covered by the mapping. AVERAGE resolves to SUM divided by COUNT, so both must exist in the aggregation table for AVERAGE queries to hit. An aggregation miss occurs when the query requests finer detail, groups by unmapped columns, or uses complex DAX that cannot decompose into supported subqueries.
Monitoring matters here. Without visibility into hit rates, you are guessing whether the aggregation tables are working. SQL Profiler exposes an Aggregate Table Rewrite Query event with JSON output showing whether each query matched. DAX Studio and Performance Analyzer provide lighter-weight alternatives for checking aggregation usage during report development.
Incremental Refresh for Managing Large Import Tables
When Import tables grow beyond a few gigabytes, full refreshes become expensive. A 20 GB table that refreshes completely eight times a day consumes hours of capacity time and hammers the source with redundant reads. Incremental refresh solves this by partitioning the table by date and refreshing only the recent window.
Configuration starts with two reserved parameters: RangeStart and RangeEnd. You create them in Power Query, reference them in a Table.SelectRows filter, and then define the refresh policy. The policy has two parts: a store period (how far back to keep historical data) and a refresh period (the rolling window that gets reprocessed each cycle). Power BI creates partitions automatically based on these settings. Historical partitions consolidate over time into coarser grain. Recent partitions stay granular.
One requirement is non-negotiable: query folding. The RangeStart and RangeEnd filters must fold to the source as native query predicates. If they do not fold, Power BI pulls the entire table on every refresh and filters locally, defeating the purpose entirely. Always verify folding with the View Native Query option in Power Query Editor before publishing.
Premium and PPU licenses unlock additional capabilities. Hybrid tables add a DirectQuery partition for the most recent data that has not yet been refreshed, giving near-real-time freshness on top of an Import model. XMLA endpoints allow external tools like Tabular Editor or Azure Data Factory to manage partitions directly. Pro licenses support basic incremental refresh but cap the refresh window at 2 hours and lack XMLA-based partition control.
DirectQuery to SAP and Other Enterprise Sources
DirectQuery’s real-time architecture makes it a natural fit for enterprise source systems where data replication is restricted by policy or where the dataset simply exceeds what Import can handle. SAP HANA, SAP BW, Azure Synapse, Snowflake, and BigQuery all support DirectQuery connections from Power BI.
SAP sources present specific challenges. SAP’s query layer adds complexity that can interfere with query folding, and the transformations needed to reshape SAP data for Power BI often require careful staging. Metrica’s Power BI Connector for SAP addresses these integration challenges by providing an optimized extraction path that preserves query folding and handles SAP-specific data structures without requiring middleware.
Regardless of the source, DirectQuery performance depends heavily on the remote engine’s optimization. Columnstore indexes on SQL Server and Azure SQL databases accelerate the analytical query patterns Power BI generates. Snowflake and BigQuery benefit from elastic scaling but may introduce cold-start latency on the first query after a cluster suspends. Matching the source’s strengths to your query patterns is as important as choosing the right storage mode.
Storage Mode Decision Framework for Enterprise Power BI Models
Choosing a storage mode comes down to four variables: data volume, freshness requirements, DAX complexity, and source system tolerance for analytical query load. Here is how those variables map to each option.
Start with Import mode when the compressed dataset fits within your license limit and daily or hourly freshness is acceptable. For most reporting workloads under 10 GB compressed, Import delivers the best combination of performance and DAX flexibility with the least complexity. Pro licenses handle models up to 1 GB compressed. PPU and Premium extend that range significantly.
Move to DirectQuery when the data must be current at query time and the source system can absorb the load. This works well for scenarios like live operational monitoring, compliance dashboards that must reflect the latest state, or datasets too large to import. Accept the latency cost and design reports with fewer visuals per page to keep query volumes manageable.
Choose a composite model when you need both fast aggregated performance and access to fine-grained detail. The classic pattern is an Import aggregation table covering 80% of user queries (monthly summaries, category rollups) with a DirectQuery detail table handling the remaining drill-through requests. This approach works especially well for datasets in the 10 to 100 GB range where pure Import hits size limits and pure DirectQuery is too slow for everyday use.
Consider Live Connection when a governed semantic model already exists in Analysis Services or the Power BI service. Thin reports connected via Live Connection reuse the central model’s logic, security, and refresh infrastructure. If you need to extend the model with local tables, converting to a composite model via “Make changes to this model” preserves the live connection while adding flexibility.
For teams running on Microsoft Fabric, Direct Lake mode offers a fourth path that combines Import-level query speed with near-real-time data access from OneLake. The details of Direct Lake, F-SKU sizing, and the migration path from Premium are covered in Microsoft Fabric for Power BI Users: Direct Lake, OneLake, and F-SKUs.
No single storage mode is correct for every model. Many production environments use Import for most datasets, composite models for the largest fact tables, and Live Connection for shared enterprise models. The right architecture usually combines two or three of these patterns, matched to the specific requirements of each dataset and the team’s capacity to manage the added complexity.
