Handling Large Datasets

Power BI offers several complementary strategies for handling large datasets, spanning storage modes, architectural patterns, and capacity features. The right approach depends on dataset size, refresh frequency, real-time requirements, and the capacity tier available.

Storage Modes

Import Mode

In Import mode, Power BI connects to a source, runs Power Query transformations, and loads the result into the VertiPaq in-memory columnar engine. VertiPaq compresses data column-by-column using dictionary encoding and run-length encoding, typically achieving 5–10x compression ratios versus raw source size. Queries are extremely fast because they run entirely in memory against pre-compressed columns.

The trade-off is capacity: Import semantic models are limited to 1 GB compressed in Pro workspaces. Data must be refreshed on a schedule rather than reflecting real-time changes.

DirectQuery Mode

In DirectQuery mode, no data is stored in the semantic model. Every visual generates a live query sent directly to the source database. This means the dataset can be arbitrarily large — Power BI never holds it in memory. Query performance is bounded by the source system's speed and the network round-trip, making it less interactive than Import for complex visuals.

DirectQuery is the right choice when data cannot leave the source system, when real-time accuracy is required, or when the dataset is simply too large to import.

DirectLake Mode (Microsoft Fabric, 2023+)

DirectLake is the most significant addition for large-scale scenarios in 2026. Instead of importing data or querying a remote database, DirectLake reads Delta Parquet files directly from OneLake — Microsoft Fabric's unified storage layer. VertiPaq streams column segments into memory on demand: the first query against a cold cache incurs a load latency, but once segments are warm, query performance approaches Import speed.

Key implications:

• Requires a Microsoft Fabric Lakehouse or Warehouse as the data source.
• Eliminates the import/refresh bottleneck — the semantic model always reflects the latest Delta table state without a scheduled refresh cycle.
• Removes the size constraints of Import mode because data lives in OneLake storage, not in model memory.

Architectural Patterns for Large Data

Composite Models

A single semantic model can mix Import, DirectQuery, and DirectLake tables. The common pattern is to place dimension tables in Import mode (fast filtering and grouping, small row counts, high compression benefit) while keeping large fact tables in DirectQuery or DirectLake (no size limit). Relationships between storage modes require careful configuration — cross-storage-mode filtering can introduce overhead if not managed.

Aggregations

Pre-built summary tables stored in Import mode sit alongside a large DirectQuery fact table. Power BI's query engine automatically routes a query to the aggregation table when the query granularity matches (e.g., sales by month and region), and falls back to DirectQuery only for row-level detail. This provides Import-speed performance for the vast majority of analytical queries while retaining the ability to drill to granular data.

Incremental Refresh

Rather than reloading an entire table on each scheduled refresh, incremental refresh partitions the table by date range. Historical partitions are retained unchanged; only the recent window is re-imported on each cycle. This dramatically reduces refresh duration and resource consumption for large historical tables. Requires defining RangeStart and RangeEnd parameters in Power Query, and is available only on Premium, PPU, or Fabric capacities.

Large Dataset Storage Format

On Premium and Fabric capacities, enabling the large dataset storage format allows semantic models to exceed the standard 1 GB limit. Data is stored on disk and paged into memory as needed, rather than being held entirely in memory. This enables models of tens of gigabytes or more in Import mode.

Choosing the Right Strategy

Scenario	Recommended approach
Dataset fits in memory, fast refresh acceptable	Import mode
Real-time data or data residency constraints	DirectQuery
Very large data in Microsoft Fabric, near-real-time	DirectLake
Mix of slowly-changing dimensions and large facts	Composite model
Large DirectQuery fact table with mostly aggregate queries	Aggregations
Large Import table with frequent, partial updates	Incremental refresh

Data modeling in Power BI is the process of defining tables, relationships, hierarchies, measures, and calculations that form the semantic model — the central analytical artifact that sits between raw data sources and the reports, dashboards, and workbooks that consume it. A well-designed semantic model hides data complexity from report authors and enforces consistent business logic across all downstream consumers.

In 2026, the semantic model is a first-class Fabric artifact: published once to a Fabric workspace, it can be consumed by multiple Power BI reports, Excel workbooks via Analyze in Excel, Paginated Reports, and third-party tools via the XMLA endpoint — without duplicating transformation logic or measures.

Core Modeling Elements

Tables and Schema Design

A semantic model contains fact tables and dimension tables. Fact tables hold transactional or measurable events (orders, transactions, sensor readings) and are typically wide in row count but narrow in columns — mostly foreign keys and numeric measures. Dimension tables hold descriptive attributes (customers, products, dates, geography) and are narrower in row count but richer in attributes.

The star schema is the recommended design: one or more fact tables at the center, each surrounded by directly related dimension tables. Star schemas produce simpler DAX, faster VertiPaq query plans, and more predictable filter propagation than snowflake or flat denormalized designs.

Relationships

Relationships define how tables join. Power BI best practices:

• Prefer one-to-many relationships (dimension to fact), with the "one" side filtered and the filter propagating to the "many" side.
• Avoid many-to-many relationships when possible — they introduce ambiguity and performance overhead. Resolve them with a bridge table where feasible.
• Use bidirectional cross-filtering sparingly. It is sometimes necessary for role-playing dimensions or complex filtering scenarios, but can cause unexpected query results and circular dependency errors.

Hierarchies

Hierarchies group columns into drill-down paths — a Date hierarchy (Year → Quarter → Month → Day) or a Geography hierarchy (Country → State → City) — enabling users to explore data at different levels of granularity within a single visual.

DAX Measures and Calculated Columns

Measures are DAX expressions evaluated at query time in the context of the current filter and grouping. They are the preferred way to express business logic because they are computed on demand and do not consume model memory.

Calculated columns are DAX expressions evaluated at refresh time and stored in the model alongside imported data. Use calculated columns only when the value needs to be used as a slicer, filter, or relationship key — not for aggregations that could be measures.

Calculation Groups

Calculation groups are a powerful modeling feature for reusable time intelligence. A single "Time Intelligence" calculation group can define items like Year-to-Date, Month-over-Month, Prior Year, and Rolling 12 Months. Any existing measure in the model can be dynamically modified by a calculation group item at query time — avoiding the need to create dozens of individual YTD, MoM, and PY variants of every base measure. Calculation groups also provide a consistent, maintainable location for all time intelligence logic.

Why Star Schema Matters for Large Data

VertiPaq's query engine (xVelocity) is optimized for star schema joins:

• Fewer joins mean simpler query plans and faster execution.
• Dimension table columns (e.g., product category, country name) have limited unique values and compress extremely well under dictionary encoding.
• Fact tables remain narrow — foreign keys are integers (highly compressible) and measures are numeric. No redundant text columns that would inflate cardinality.

Denormalized flat tables or snowflake schemas force the engine to join across more hops, increase cardinality in fact columns, and often produce slower DAX execution.

2026-Specific Additions

TMDL (Tabular Model Definition Language)

TMDL is the modern format for source-controlling semantic models. It serializes model metadata as human-readable text files (one file per table, one per relationship, etc.) that can be stored in git, reviewed in pull requests, and diffed meaningfully. It replaces the legacy .bim JSON format, which was a single large opaque file poorly suited to version control. TMDL is natively supported in Power BI Desktop and the Fabric portal as of 2024–2025.

Shared Semantic Models in Microsoft Fabric

In a Fabric workspace, a semantic model can be certified and shared as the single source of truth for an entire organization. Multiple reports connect to it rather than each maintaining their own model. Fabric's OneLake integration means the model can reference Lakehouse Delta tables and Warehouse views directly via DirectLake mode — the model reflects the latest data without a scheduled import refresh.

Calculation Groups (Widespread Adoption)

Previously available only in Analysis Services and Premium, calculation groups are now a standard modeling tool across all Power BI tiers with Fabric. Their adoption resolves one of the most common model bloat issues: measure proliferation caused by duplicating every base measure into a YTD, MoM, and PY variant.

When using Import mode, Power BI retrieves data from a source system, applies transformations, and loads the result into its in-memory VertiPaq engine. For large datasets, the process involves several layers each with its own scaling strategies.

The Import Pipeline

Power Query (M Language)

The first stage is Power Query, Power BI's transformation layer written in the M language. Power Query connects to the source, applies transformation steps (filtering rows, renaming columns, merging tables, changing data types), and produces a clean, shaped result to load into the model.

A critical optimization for large datasets is query folding: Power Query's ability to translate M transformation steps into native SQL (or the source's equivalent query language) and push that work back to the source system. When query folding is active, filtering and aggregation happen at the source before any data is transferred to Power BI, reducing network load and memory consumption significantly. Not all transformation steps fold; steps that break folding (such as certain custom column operations) should be placed as late as possible in the query to preserve folding for earlier steps.

VertiPaq Column Store

Once Power Query delivers the shaped data, it is loaded into VertiPaq, Power BI's in-memory columnar storage engine. VertiPaq compresses each column independently using dictionary encoding (mapping unique values to integer IDs) and run-length encoding (collapsing consecutive repeated values into value-count pairs). Typical compression ratios are 5-10x versus raw source size, meaning a 10 GB source table might occupy around 1-2 GB in memory.

Size Limits

• Pro workspaces: semantic models are limited to 1 GB compressed. This is the single most common constraint for Import-mode large datasets in standard Power BI Service.
• Premium / PPU / Fabric capacities: enabling the large dataset storage format allows models to grow well beyond 1 GB into the tens of gigabytes by storing data on disk and paging it into memory as needed.
• DirectLake (Fabric only) sidesteps these limits entirely by not storing data in the model at all.

Strategies for Large Import Datasets

Incremental Refresh

Incremental refresh is the primary tool for keeping large Import tables manageable. Instead of reloading every row on each scheduled refresh, the table is partitioned by date range. Historical partitions (e.g., data older than 2 years) are kept in model memory untouched. Only the current window (e.g., the last 30 days) is re-imported on each cycle.

Setup requires two Power Query parameters, RangeStart (DateTime) and RangeEnd (DateTime), used to filter the source query. Power BI Service recognizes these parameters and manages partition boundaries automatically. Incremental refresh requires Premium, PPU, or Fabric capacity.

With Fabric, incremental refresh can also detect changed rows using change detection, further reducing the refresh scope beyond what date-range partitioning alone provides.

Fabric Dataflows Gen2 and Data Factory Pipelines

In the Microsoft Fabric ecosystem, large-scale data preparation is handled outside the semantic model entirely:

• Data Factory pipelines orchestrate movement and transformation of large volumes of data into Fabric Lakehouse Delta tables or Warehouse tables. These pipelines handle enterprise-scale ETL with parallelism, retries, and monitoring.
• Dataflows Gen2 bring Power Query to the cloud with horizontal scale-out. A Dataflow Gen2 applies M transformations to large datasets and writes the output to a Lakehouse Delta table. The semantic model then accesses that table via DirectLake mode, avoiding a full import into the model.

This pattern — pipeline or Dataflow Gen2 to Lakehouse Delta table to DirectLake semantic model — is the recommended architecture for very large datasets in Fabric as of 2026.

Dataflow Gen1 (Power BI Service)

The original Dataflow Gen1 (available in standard Power BI Service without Fabric) provides a cloud-based Power Query layer for centralizing and reusing data preparation logic. A Dataflow Gen1 runs transformations in the cloud and stores results as dataflow entities, which multiple semantic models can reference as a source, eliminating duplicated M code across models. Output lands in Azure Data Lake Storage Gen2 and is imported into semantic models on a schedule.

Choosing the Right Import Strategy

Dataset size / scenario	Approach
Small to medium, Pro workspace	Standard Import with Power Query folding
Large table with historical + recent data	Incremental refresh (Premium / Fabric)
Very large, Fabric environment	Dataflow Gen2 or pipeline to Lakehouse then DirectLake
Reusable centralized transforms, standard Power BI	Dataflow Gen1 to Import
Exceeds 1 GB, Premium capacity	Large dataset storage format

Power BI uses the VertiPaq in-memory columnar storage engine (shared with Analysis Services Tabular and Azure Analysis Services) to store and query Import mode semantic models. VertiPaq compresses each column independently, achieving dramatic size reductions that allow large datasets to fit in memory and be queried at high speed.

How VertiPaq Compresses Data

1. Dictionary Encoding

The first compression step converts raw column values into integer IDs. VertiPaq builds a dictionary that maps each unique value in a column to a compact integer. The column itself stores only the integer IDs rather than the original strings or numbers.

For a column containing values like "North", "South", "East", and "West", the dictionary might assign North=0, South=1, East=2, West=3. Every row stores a 2-bit integer instead of a multi-byte string. Columns with many repeated values (low cardinality) benefit enormously. High-cardinality columns where nearly every row is unique (such as an order GUID) benefit less, because the dictionary itself becomes large.

2. Run-Length Encoding (RLE)

After dictionary encoding, VertiPaq applies run-length encoding to the integer ID sequence. RLE replaces runs of consecutive identical values with a (value, count) pair. A column where the first 10,000 rows all have the ID for "North" is stored as a single entry: (0, 10000).

VertiPaq automatically sorts and segments data within each column to maximize RLE compression — rows are reordered internally to cluster identical values together. This is why dimension tables sorted by their primary attribute before import tend to compress better.

3. Column Segmentation

Large columns are divided into segments of approximately 1 million rows each. Each segment is compressed independently. Segmentation enables:

• Parallel query processing: multiple CPU cores can scan different segments simultaneously.
• Selective segment loading: in DirectLake mode, only the segments needed to answer a query are loaded into memory.

Practical Design Implications

Low-cardinality columns compress best. Integers, short codes, and categorical text values with few unique entries compress far better than free-text or high-precision numeric columns. Where possible, store status codes as integers rather than strings.

Avoid high-cardinality columns that are not needed. A timestamp column with millisecond precision may have a unique value per row, making dictionary encoding nearly useless and RLE ineffective. Truncating to day or hour granularity can dramatically reduce cardinality and improve compression.

Remove unused columns. This is one of the highest-impact optimizations available. Hidden columns still consume memory even if they never appear in a visual or measure. Removing them reduces model size and improves query performance.

Sort dimension tables before import. Sorting a dimension table by its primary grouping attribute (e.g., ProductCategory before ProductSubcategory) before loading helps RLE find longer runs of repeated values, improving compression.

Narrow fact tables compress better. Keeping fact tables to foreign key integers and numeric measures avoids storing redundant text attributes alongside each row. Text attributes belong in dimension tables where they compress once, not replicated across millions of fact rows.

Compression Ratios in Practice

Typical Power BI Import models achieve 5-10x compression versus the raw source data size. A well-optimized model with low-cardinality columns, removed unused fields, and sorted dimensions can exceed this. A poorly optimized model with many unique-per-row string columns may compress much less. Checking model size in Power BI Desktop's "Model size" view (via DAX Studio or the built-in diagnostics) against source table sizes reveals which columns are consuming disproportionate space.

DirectLake and Parquet Compression

In Microsoft Fabric's DirectLake mode, data is not stored in the VertiPaq model at all. Instead, it lives as Delta Parquet files in OneLake. Parquet uses similar columnar compression techniques — dictionary encoding plus byte-stream compression (Snappy or Zstandard) — so on-disk storage is already compact.

When a DirectLake query accesses data, VertiPaq streams column segments from Parquet into memory on demand and caches them. Those cached segments benefit from VertiPaq's own in-memory representation. The result is a two-layer compression story: Parquet compression on disk (minimizing storage cost and read I/O) and VertiPaq caching in memory (enabling fast query performance once segments are warm). This makes DirectLake highly efficient for both storage cost and query speed on very large datasets.

1. Choose the right storage mode: Import mode loads data into the in-memory VertiPaq engine for the fastest query performance, but the full dataset must fit within capacity memory. DirectQuery leaves data in the source and queries it live — suitable for very large datasets or when real-time accuracy is required, at the cost of query latency. DirectLake (Fabric) reads Delta Parquet files directly from OneLake, delivering near-Import speed without a full copy of the data in the semantic model.

2. Star schema data model: Model data as a star schema with narrow fact tables (metrics and foreign keys only) joined to wide dimension tables. Integer surrogate keys compress far better than string keys and produce simpler query plans. Avoid snowflake schemas (dimensions joined to other dimensions) unless the data genuinely demands it — each extra relationship adds join overhead.

3. Incremental refresh: Configure incremental refresh policies so that only new or changed partitions are reloaded on each scheduled refresh cycle, rather than truncating and reloading the full table. This dramatically reduces refresh duration and peak memory pressure. Incremental refresh requires Power BI Premium, Premium Per User (PPU), or Fabric capacity.

4. Aggregation tables: Create pre-summarized tables (aggregations) in Import mode that sit alongside a large DirectQuery fact table. Power BI automatically routes aggregation-grain queries to the Import table and falls back to DirectQuery only for detailed row-level lookups. This gives near-Import speed for typical summary visuals while preserving drill-through capability.

5. DAX optimization: Write measures rather than calculated columns wherever possible — measures are computed at query time against the filtered context rather than stored for every row. Use VAR to name and reuse intermediate results instead of recalculating the same sub-expression multiple times. Prefer simple scalar or table expressions as filter arguments in CALCULATE over FILTER(ALL(...), ...) patterns, which force row-by-row iteration over large tables. Avoid high-cardinality iterators such as SUMX over millions of rows unless the calculation truly requires row context.

6. Reduce model size: Remove every column and table not required by reports or security rules. Hidden columns still consume RAM — hiding them only affects the report canvas, not the VertiPaq store. Columns with lower cardinality (fewer distinct values) compress much more efficiently; converting high-cardinality text columns to integer keys where possible yields significant size savings.

7. Query folding in Power Query: Query folding pushes transformation steps back to the source system as native SQL (or the source's query language), so the source does the heavy lifting and only the result set travels over the network. Avoid steps that break folding — adding a custom column that references a non-foldable step, using certain M functions, or merging queries against incompatible sources. The "View native query" right-click option in Power Query Editor confirms whether a step is still folding.

8. Semantic model partitioning: In Fabric or Premium capacities, large tables can be divided into multiple partitions that are processed in parallel during refresh. Partitioning by date range is the most common pattern — partitions covering historical data that never changes can be set to read-only, and only the current partition is reloaded on each cycle.

9. Performance Analyzer and DAX Studio: Use the Performance Analyzer pane in Power BI Desktop to capture render times for each visual and identify which visuals generate the slowest DAX queries. Copy slow query text into DAX Studio to analyze the split between storage engine time (VertiPaq scans) and formula engine time (interpreted row-by-row calculation), inspect xmSQL storage engine queries, and test rewrites without redeploying the report.

10. Fabric-specific data preparation: For very large datasets, move transformation and aggregation logic upstream into Fabric Dataflows Gen2, Spark notebooks, or Data Factory pipelines. Distributed processing in Fabric can handle data volumes and complexity that would be impractical inside a Power Query refresh, delivering a clean, compact dataset to the semantic model layer.