You've been asked to build a single Power BI model that serves the entire enterprise — sales analysts who need yesterday's actuals, operations teams who need up-to-the-minute inventory movements, and executives who want a five-year trend rendered in seconds. The traditional answer has been to split this into separate models: one for real-time dashboards, one for historical reporting, with some awkward bridging mechanism between them. That answer is wrong, expensive to maintain, and it creates the kind of analytical inconsistency that makes stakeholders stop trusting your numbers.
Power BI Premium's combination of Large Format Datasets (LFD) and Hybrid Tables changes this equation fundamentally. A hybrid table is a single table that contains both fully-processed, historically-cached partitions and a DirectQuery partition that always reads live data — no separate models, no data duplication, no architectural gymnastics. When your CEO asks "what are our revenue numbers right now?" and your CFO asks "how does that compare to Q3 2019?" both questions get answered from the same semantic model, with the same measures, the same security roles, and the same business logic. That's the promise, and when implemented correctly, it actually delivers.
This lesson teaches you how to build that architecture from the ground up. By the end, you'll understand not just the how but the deep why — the storage engine mechanics that make hybrid tables work, the partitioning strategies that determine whether your model performs or crawls, and the operational patterns that keep it healthy at enterprise scale.
What you'll learn:
Before working through this lesson, you should be comfortable with:
Before we look at the solution, let's be precise about why serving real-time and historical data from one model has historically been difficult. The diagnosis matters because it explains every design decision we'll make.
The VertiPaq storage engine — the columnar, in-memory engine that makes Power BI Import mode fast — is a batch system. Data gets loaded, compressed, and encoded into memory. Once loaded, it's extraordinarily fast for aggregations. But it's frozen in time. The moment you refresh a partition, VertiPaq reprocesses it from scratch, and during that window your data has a temporal boundary. If your last full refresh finished at 11:45 PM, then at 2:00 PM the next day your model is already 14 hours stale.
DirectQuery solves the staleness problem. Every query hits the source system directly, so you're always reading current data. But the cost is latency: every single DAX query must be translated into one or more SQL queries, shipped to the source, results retrieved, and then processed by the formula engine. For high-cardinality historical data, this is brutally slow. A time intelligence calculation that spans three years of daily sales data — billions of rows — will time out or produce a result that makes users give up and build an Excel pivot table instead.
The naive solution — "use aggregations and DirectQuery together" — gets you partway there but doesn't give you true hybrid storage. Aggregations are pre-computed cached tables that answer high-level queries, but the underlying detail table is still fully DirectQuery. You're caching summaries, not a genuinely partitioned mix of stored and live data.
Hybrid tables fix this at the partition level. The historical partitions are Import-mode VertiPaq partitions: fully compressed, lightning fast. The real-time partition is a DirectQuery partition against the same source table. The query planner knows about both and routes appropriately — historical range questions go to VertiPaq, recent-window questions go DirectQuery, and cross-partition queries that span both are automatically federated. This is the correct architectural answer.
"Large Format Dataset" is Microsoft's branding for datasets stored using the enhanced metadata format that supports files larger than the default 10 GB limit per dataset in Power BI Premium. Without LFD enabled, even Premium capacities cap out at 10 GB compressed per dataset. With LFD enabled, you can go up to 400 GB per dataset (the practical ceiling depends on your P or EM SKU's node memory).
But LFD isn't just about size. It also changes how the dataset file is stored on the Premium capacity node. Standard datasets use a .pbix-derived in-memory model that must be fully loaded into RAM to be accessible. LFD-enabled datasets use a different on-disk format that supports paging — portions of the model can be loaded and unloaded from memory on demand, similar to how a database buffer pool works. This is what allows a 200 GB dataset to run on a P2 node with 25 GB of RAM: the most-queried partitions stay hot in memory, and cold historical partitions page out to disk.
This paging behavior has critical implications for your partitioning strategy, which we'll return to shortly.
Large Format Datasets must be enabled at two levels: the workspace setting and the dataset setting.
At the workspace level, navigate to your Premium workspace settings in the Power BI Service. Under the Premium tab, you'll find a toggle labeled Large dataset storage format. Enable this. This is a workspace-level default; it tells the service to use LFD for new datasets published to this workspace.
For existing datasets or for explicit control, you can set the storage format at the dataset level via the dataset settings page. Navigate to the dataset in your workspace, open its settings, and under Large dataset storage format, switch the toggle to On. You'll see a note warning you that this change is permanent — once a dataset is converted to LFD, you cannot convert it back without republishing from Desktop.
Warning: LFD datasets cannot be downloaded as .pbix files from the Power BI Service. If you need to edit the model, you must connect to it via XMLA endpoint or work from the original .pbix. Plan your source-of-truth management accordingly before you enable LFD in production.
The XMLA endpoint is central to managing LFD hybrid models at scale. Make sure XMLA read/write is enabled on your Premium capacity. In the Power BI Admin Portal, under Capacity settings, find your capacity and enable XMLA Endpoint set to Read Write.
Partitioning is where most enterprise implementations go wrong. Get this right and everything else is manageable. Get it wrong and you'll rebuild from scratch six months later.
A hybrid table has a specific structural requirement: exactly one DirectQuery partition, and one or more Import-mode partitions. The DirectQuery partition is always the "hot" window — the most recent data. The Import partitions are the "cold" historical data.
Here's how to think about the time boundaries:
Timeline:
|--- Historical Import Partitions ---|--- Real-Time DQ Partition ---|
^
"Polled Timestamp"
(last full refresh)
The overlap point between your last Import partition and your DQ partition is critical. Your DQ partition's query must start at exactly the same timestamp where your last Import partition ends. If there's a gap, you'll have missing data. If there's overlap, you'll get double-counting in aggregations.
Before you decide on partition granularity, you need to understand your query patterns:
A pattern that works well for most enterprise fact tables:
| Partition | Type | Coverage | Refresh Frequency |
|---|---|---|---|
Sales_2019 |
Import | Full year 2019 | Yearly (or never) |
Sales_2020 |
Import | Full year 2020 | Yearly (or never) |
Sales_2021 |
Import | Full year 2021 | Yearly (or never) |
Sales_2022 |
Import | Full year 2022 | Yearly (or never) |
Sales_2023 |
Import | Full year 2023 | Yearly (or never) |
Sales_2024_Q1 |
Import | Jan–Mar 2024 | Quarterly |
Sales_2024_Q2 |
Import | Apr–Jun 2024 | Quarterly |
Sales_2024_Q3 |
Import | Jul–Sep 2024 | Quarterly |
Sales_2024_Rolling |
Import | Last 90 days | Every 4 hours |
Sales_RealTime |
DirectQuery | Last 4 hours | N/A — always live |
The key insight here: the rolling partition overlaps in concept with the DirectQuery partition in time, but the partition boundaries must be cleanly defined so they don't actually overlap in data. The Rolling Import partition covers data up to (but not including) 4 hours ago. The DirectQuery partition covers the last 4 hours exactly.
Power BI's incremental refresh mechanism is the practical tool for managing Import partitions. It automates partition creation and management using two special parameters — RangeStart and RangeEnd — that you define in Power Query.
In Power BI Desktop, go to Home > Manage Parameters > New Parameter. Create two parameters:
RangeStart, Type: Date/Time, Current Value: the start of your historical window (e.g., 1/1/2019 12:00:00 AM)RangeEnd, Type: Date/Time, Current Value: the current date/timeThen in your fact table query, filter your date column using these parameters:
let
Source = Sql.Database("your-server.database.windows.net", "SalesDB"),
SalesTable = Source{[Schema="dbo",Item="FactSales"]}[Data],
#"Filtered Rows" = Table.SelectRows(
SalesTable,
each [OrderDateTime] >= RangeStart and [OrderDateTime] < RangeEnd
)
in
#"Filtered Rows"
The Power Query filter must use >= for the start and < for the end. This closed-open interval [RangeStart, RangeEnd) is what prevents data duplication at partition boundaries. Microsoft's refresh engine respects this pattern when it builds partition queries.
Tip: The column you filter on (
OrderDateTimein the example) must be the same column you use as your incremental refresh key. Don't filter on a derived column or a relationship key — use the raw timestamp column that exists in the source.
Right-click your fact table in the Fields pane and select Incremental Refresh. The Incremental Refresh dialog opens. Configure it as follows:
5 Years. This is the total historical window.90 Days. Partitions within this window get refreshed on each scheduled refresh.That last checkbox is the hybrid table toggle. When you enable it, Power BI adds the DirectQuery partition configuration. The DQ window is automatically the period between the end of your last incremental refresh partition and "now." You can set this to cover the last 1 Day or last few Hours depending on your latency requirements.
Warning: When you enable the DirectQuery real-time option, the entire table becomes a mixed-mode table. This has implications for relationships. Relationships to this table must use integer or string keys, not complex types, and they must be configured to work correctly with DirectQuery. Test your relationship behavior explicitly after enabling hybrid mode.
Publishing from Desktop gives you the initial deployment, but ongoing management of a production LFD hybrid model is done through the XMLA endpoint. Here's why: the Desktop .pbix file has a 10 GB limit and cannot hold the full historical data. What you publish is the model definition (schema, relationships, measures, partitioning policy), not the data. The data gets loaded by the Premium capacity's refresh engine.
Save your .pbix, then publish to your LFD-enabled Premium workspace. The publish operation sends the model definition. Once published, go to the dataset settings and confirm that:
Trigger a manual refresh from the workspace. The first refresh after enabling incremental refresh will be a full historical load — this can take hours for large tables. The Premium capacity will create individual partitions as it processes the date ranges.
After the first refresh, connect Tabular Editor to your dataset via the XMLA endpoint. In Tabular Editor, go to File > Open > From DB (Read/Write). Enter your XMLA endpoint URL (format: powerbi://api.powerbi.com/v1.0/myorg/WorkspaceName) and authenticate.
Navigate to Tables > FactSales in the left panel. Expand the Partitions node. You should see the Import partitions that were created by incremental refresh, plus one DirectQuery partition. The DQ partition will have a Mode property set to DirectQuery, while the Import partitions will show Import.
Click on a partition to inspect its properties. The Import partition's Query property will show the folded M expression. The DirectQuery partition's Query will show the SQL that runs against your source for the live window.
You can manually add, edit, or remove partitions here. For example, if you want to create a yearly archive partition that covers 2018 data (before your incremental refresh window starts), you'd add a partition manually:
In Tabular Editor, right-click Partitions under FactSales and select Add Partition. Set the partition name to FactSales_2018, set Mode to Import, and set the Query to:
let
Source = Sql.Database("your-server.database.windows.net", "SalesDB"),
SalesTable = Source{[Schema="dbo",Item="FactSales"]}[Data],
#"Filtered Rows" = Table.SelectRows(
SalesTable,
each [OrderDateTime] >= #datetime(2018, 1, 1, 0, 0, 0)
and [OrderDateTime] < #datetime(2019, 1, 1, 0, 0, 0)
)
in
#"Filtered Rows"
Save the change (Ctrl+S commits to the model, triggering a metadata-only update). Then process this partition via the Model > Process Table menu or by right-clicking the partition and selecting Process Partition.
For truly automated partition management in production, use the Tabular Object Model (TOM) via a .NET script or a PowerShell module like SqlServer. Here's a C#-style TOM script (runnable in Tabular Editor's Advanced Scripting window) that creates a yearly archive partition programmatically:
// Tabular Editor C# Script
// Creates a new annual archive partition for FactSales
var table = Model.Tables["FactSales"];
int archiveYear = 2018;
string partitionName = $"FactSales_{archiveYear}";
// Check if partition already exists
if (table.Partitions[partitionName] != null)
{
Info($"Partition {partitionName} already exists. Skipping.");
return;
}
// Create the new M partition
var newPartition = table.AddMPartition(partitionName);
newPartition.Mode = ModeType.Import;
newPartition.MExpression = $@"
let
Source = Sql.Database(""your-server.database.windows.net"", ""SalesDB""),
SalesTable = Source{{[Schema=""dbo"",Item=""FactSales""]}}[Data],
#""Filtered Rows"" = Table.SelectRows(
SalesTable,
each [OrderDateTime] >= #datetime({archiveYear}, 1, 1, 0, 0, 0)
and [OrderDateTime] < #datetime({archiveYear + 1}, 1, 1, 0, 0, 0)
)
in
#""Filtered Rows""
";
Info($"Partition {partitionName} created. Save and process to load data.");
Run this script, save the model, then process the partition. This pattern is the foundation for automated partition lifecycle management — you can run similar scripts via Azure Automation or Azure DevOps pipelines to create new yearly partitions at the end of each year.
Understanding how Power BI routes queries to a hybrid table makes the difference between confident troubleshooting and frustrated guessing.
When a user submits a DAX query (by interacting with a visual), the Analysis Services engine — which Power BI Premium runs underneath — analyzes the filter context and determines which partitions are relevant.
For a query like "Total Sales for Q4 2022":
FactSales_2022 (an Import partition) covers this range.For a query like "Total Sales in the last 2 hours":
For a query that crosses the boundary — "Total Sales for the past 7 days" when your DQ window is 4 hours and your rolling Import partition covers the prior 90 days:
This cross-partition federation is automatic and transparent. You don't write different DAX for historical vs. real-time queries. Your measures just work.
Tip: Use Performance Analyzer in Power BI Desktop or SQL Server Profiler (connected to the XMLA endpoint) to observe this behavior explicitly. You'll see both DirectQuery SQL events and VertiPaq scan events for the same visual when it spans the partition boundary. This is extremely useful for validating that your boundaries are where you think they are.
Cross-partition queries are powerful but not free. The formula engine must hold intermediate results from both the VertiPaq scan and the DirectQuery result in memory before merging. If your DQ query returns 5 million rows (because a user queried at transaction grain for the current month), the formula engine now has 5 million rows sitting in RAM waiting to be aggregated.
Design your real-time partition to have a narrow, reasonable time window. The purpose of DirectQuery here is freshness, not breadth. A 4-hour to 24-hour DirectQuery window is typical. If someone needs the last 30 days at transaction grain, they should be using your rolling Import partition, which is compressed and fast.
If you see performance problems in cross-boundary queries, check the time window of your DQ partition first. Narrowing it is almost always the right fix.
The DirectQuery partition hits your source table directly. This means your source table's indexing matters enormously. At minimum, your timestamp column (the partition key) must be indexed. For Azure SQL Database:
-- Create a filtered index for the real-time window on the source table
-- This covers the DirectQuery partition's query pattern
CREATE INDEX IX_FactSales_OrderDateTime_Recent
ON dbo.FactSales (OrderDateTime)
INCLUDE (CustomerID, ProductID, Quantity, UnitPrice, TotalAmount)
WHERE OrderDateTime >= DATEADD(DAY, -2, GETUTCDATE());
A filtered index on the recent time window dramatically speeds up DQ partition queries because the database engine doesn't have to scan the full historical index.
Also consider a columnstore index on your source fact table for the Import partition refreshes. Columnstore indexes make the data reads during partition processing significantly faster:
-- Clustered columnstore on the source fact table
-- Benefits both: incremental refresh partition loading AND DirectQuery performance
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales;
-- But you still need a non-clustered rowstore index on the time column
-- for the narrow filtered queries in the DQ partition
CREATE NONCLUSTERED INDEX IX_FactSales_OrderDateTime
ON dbo.FactSales (OrderDateTime ASC);
For the Import partitions, your column cardinality and data types determine compression ratios. High cardinality columns (like transaction GUIDs or free-text descriptions) compress poorly and consume disproportionate memory. Audit your import partitions with DAX Studio's VertiPaq Analyzer.
Connect DAX Studio to your deployed dataset (via XMLA), open VertiPaq Analyzer from the Advanced menu, and run the analysis. Look at the Column Size column in the output. Columns that are unexpectedly large relative to their row count indicate compression problems.
Common fixes:
The DirectQuery partition inherits the dataset's DirectQuery timeout setting. In the Power BI Service, navigate to your dataset settings and find the Query timeout setting. The default is 225 seconds. For a tightly-scoped real-time partition, 30–60 seconds is usually plenty. Reducing this prevents runaway queries from blocking your capacity node.
You can also set this via XMLA by modifying the QueryTimeout property on the datasource object in the model — useful if you're managing settings programmatically.
With LFD-enabled datasets, the capacity engine uses a priority-based eviction policy. Hot partitions (frequently queried) stay in memory; cold partitions page to disk. You don't control this directly, but you influence it through query patterns and partition layout.
The DirectQuery partition never occupies Import memory — it has no in-memory representation. But when cross-partition queries run, the DQ results do temporarily consume formula engine memory. Size your Premium SKU with this in mind.
A P2 node (50 GB RAM) comfortably handles LFD datasets up to ~150 GB compressed, assuming normal access patterns where roughly 20–30% of the data is hot at any given time. If you have use cases where broad historical scans happen frequently, you may need a P3 node to avoid excessive paging latency.
Row-Level Security (RLS) in a hybrid table has a nuance that many implementations get wrong. RLS roles defined in your Power BI model are applied at the model layer, which means:
However, this also means that the database account used by your DirectQuery data source connection must have read access to all rows that any Power BI user might be allowed to see. The RLS filter is applied after retrieval — or more precisely, it's included in the SQL WHERE clause — but the connection credential must have broad read rights.
-- Example: the service account used by your Power BI DirectQuery connection
-- needs SELECT on all rows Power BI might query
GRANT SELECT ON dbo.FactSales TO [powerbi-service-account];
-- Power BI will generate SQL like this for an RLS-filtered DQ query:
SELECT SUM(TotalAmount)
FROM dbo.FactSales
WHERE OrderDateTime >= '2024-10-15 08:00:00'
AND RegionID IN (1, 3, 7) -- This is the RLS filter translated to SQL
Warning: Never use Windows Integrated Authentication or OAuth for DirectQuery connections in a service account scenario. Use a dedicated SQL account with least-privilege SELECT rights on the relevant tables. This makes auditing, rotation, and access control straightforward.
Object-Level Security (OLS) — hiding tables or columns from certain roles — applies uniformly across both Import and DirectQuery partitions and requires no special configuration for hybrid tables.
This exercise walks you through building a hybrid table for a retail sales model. You'll need a Premium Per User workspace and an Azure SQL Database with at least one year of transactional data.
Create a table in Azure SQL Database to simulate your fact table:
CREATE TABLE dbo.FactRetailSales (
SaleID BIGINT IDENTITY(1,1) PRIMARY KEY,
OrderDateTime DATETIME2 NOT NULL,
StoreID INT NOT NULL,
ProductID INT NOT NULL,
CustomerID INT NOT NULL,
Quantity INT NOT NULL,
UnitPrice DECIMAL(10,2) NOT NULL,
DiscountAmount DECIMAL(10,2) NOT NULL DEFAULT 0,
TotalAmount AS (Quantity * UnitPrice - DiscountAmount) PERSISTED,
LoadedDateTime DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_FactRetailSales_OrderDateTime
ON dbo.FactRetailSales (OrderDateTime ASC)
INCLUDE (StoreID, ProductID, CustomerID, Quantity, UnitPrice, TotalAmount);
Populate it with at least two years of historical data plus recent data. Use a script that generates rows at varying rates — denser for business hours, sparse for weekends. The data distribution matters for realistic testing.
Open Power BI Desktop. Create the RangeStart and RangeEnd parameters as described earlier. Build your fact table query filtering on OrderDateTime:
let
Source = Sql.Database(
"yourserver.database.windows.net",
"RetailDB",
[Query = "SELECT SaleID, OrderDateTime, StoreID, ProductID,
CustomerID, Quantity, UnitPrice, TotalAmount
FROM dbo.FactRetailSales"]
),
#"Filtered Rows" = Table.SelectRows(
Source,
each [OrderDateTime] >= RangeStart and [OrderDateTime] < RangeEnd
)
in
#"Filtered Rows"
Note that we're specifying the columns explicitly in the SQL query. This prevents the SELECT * pattern that can break DirectQuery query folding when table schemas change.
Right-click FactRetailSales in the Fields pane, select Incremental refresh. Configure:
Click Apply. Notice that the table icon in the Fields pane changes to indicate the incremental refresh policy is active.
In your dataset, create a measure that explicitly tests the hybrid behavior:
Total Sales = SUMX(FactRetailSales, FactRetailSales[TotalAmount])
Create a second measure that shows whether the current filter context is touching the DirectQuery partition:
Data Freshness Note =
VAR LatestTimestamp = MAX(FactRetailSales[OrderDateTime])
VAR HoursOld = DATEDIFF(LatestTimestamp, NOW(), HOUR)
RETURN
IF(
HoursOld < 2,
"Real-time data (< 2 hours old)",
"Cached historical data"
)
Publish to your PPU workspace. Enable LFD on the dataset. Run a manual refresh. After the refresh completes (this may take 30–60 minutes for the initial full load), connect to the dataset with DAX Studio.
Run this query to validate partition behavior:
EVALUATE
SUMMARIZECOLUMNS(
DATESMTD(FactRetailSales[OrderDateTime]),
"Total Sales", [Total Sales]
)
In DAX Studio, enable Server Timings and observe the storage engine events. For months in the historical window you should see only VertiPaq storage engine events. For the current period you should see both VertiPaq events and a DirectQuery event.
Insert a new row into your Azure SQL table:
INSERT INTO dbo.FactRetailSales
(OrderDateTime, StoreID, ProductID, CustomerID, Quantity, UnitPrice)
VALUES
(SYSUTCDATETIME(), 42, 1001, 88001, 3, 49.99);
Without refreshing the dataset, re-run your DAX query filtered to today's data. The new row should appear immediately in the result — it's being read directly from SQL by the DirectQuery partition. This is the core value proposition of the hybrid table: no refresh required for real-time data.
Symptom: Your Total Sales number for the current month is slightly wrong — either too high (double counting) or too low (gaps). Users report that the numbers "don't match" their transaction system.
Cause: The boundary between your last Import partition and the DirectQuery partition has either a gap or an overlap. This typically happens when the DirectQuery partition's SQL filter uses a different timezone than the Import partition's M filter.
Fix: Align everything to UTC. Your Power Query RangeStart/RangeEnd parameters should use UTC. Your DirectQuery partition SQL should use GETUTCDATE(). Your source table timestamps should be stored as UTC. Introduce timezone conversion only at the display layer in DAX using a time zone offset column in your date table.
Symptom: The real-time portion of your dashboard shows zeros or blanks for the current period, even though data clearly exists in the source.
Cause: Usually a credentials issue. The DirectQuery partition uses the stored credentials for the dataset's data source. If those credentials expired or were never configured for DirectQuery, the partition silently returns nothing.
Fix: In dataset settings, re-enter the data source credentials. Specifically look for a warning icon next to the data source. Also check the dataset's refresh history — DirectQuery credential failures show up there as warnings, not errors.
Symptom: After enabling the hybrid table, dashboard load times increased significantly across all visuals, not just those displaying recent data.
Cause: Some DAX measures are written in a way that causes the query planner to include the DirectQuery partition even when it's not needed. Common culprits are measures that use ALL(), ALLEXCEPT(), or time intelligence functions without proper date filters, causing the query context to span all dates including the DQ window.
Fix: Add explicit date range filters to your base measures, or create a calculated table that defines the valid historical range and use it as a filter. Run Performance Analyzer to identify which visuals trigger DirectQuery events unexpectedly. Rewrite offending measures to ensure their filter context doesn't touch the DQ partition unless necessary.
-- Problematic: always touches DQ partition because ALL() removes date filter
Prior Year Sales =
CALCULATE(
SUMX(FactRetailSales, FactRetailSales[TotalAmount]),
ALL(DateTable),
SAMEPERIODLASTYEAR(DateTable[Date])
)
-- Better: restrict to confirmed complete historical data
Prior Year Sales =
CALCULATE(
SUMX(FactRetailSales, FactRetailSales[TotalAmount]),
FILTER(
ALL(DateTable),
DateTable[Date] >= DATE(YEAR(TODAY())-1, 1, 1)
AND DateTable[Date] < DATE(YEAR(TODAY()), 1, 1)
)
)
Symptom: After several months of operation, Tabular Editor shows hundreds of small partitions. Refresh times are increasing and the model is consuming more memory than expected.
Cause: The incremental refresh policy is creating daily or even hourly partitions when you configured it to store rows across a multi-year range with a short refresh window. Power BI will create a partition for each period in the refresh window, and if you set the refresh window to "last 30 days" with daily granularity, you get 30 partitions for recent data alone.
Fix: Review and adjust your incremental refresh policy granularity. In Tabular Editor, manually merge small partitions: process and combine multiple daily partitions into a single monthly partition, then delete the individual daily ones. Going forward, size your refresh window to match partition granularity — weekly granularity for a 90-day rolling window creates 13 partitions, which is manageable.
Symptom: A user who should only see data for Region 5 can see all regions when the visual is displaying data from the current day.
Cause: The RLS role filter is a DAX expression. When translated for the DirectQuery partition, some DAX functions don't translate cleanly to SQL, and the translation falls back to returning an empty result or, in some bug scenarios, an unfiltered result.
Fix: Keep RLS DAX filters simple for hybrid tables. Avoid using DAX functions that don't translate to SQL — specifically USERPRINCIPALNAME() combined with complex joins inside the filter expression. Use a pattern where USERPRINCIPALNAME() lookups are resolved in a small dimension table via Import mode, and the resulting key list is used to filter the fact table. This ensures the DirectQuery partition gets a simple WHERE RegionID IN (...) clause rather than a complex expression that breaks during translation.
A hybrid table dataset isn't fire-and-forget. Here's what a mature operational pattern looks like:
Weekly: Review the refresh history for any failed DQ credential checks or partition processing failures. Use the Power BI REST API to pull refresh history programmatically and feed it into your monitoring system:
GET https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/refreshes
Authorization: Bearer {token}
Monthly: Connect Tabular Editor to the XMLA endpoint and audit partition sizes. Archive completed monthly partitions that have moved outside the rolling refresh window — change their mode from Import to Read-Only (which prevents accidental reprocessing) and document them.
Quarterly: Re-run VertiPaq Analyzer on the model. Column size distributions shift as data patterns change. A column that was low-cardinality in year 1 might become high-cardinality in year 3 as the business grows. Catch this before it causes a memory crisis.
Annually: Review the entire partitioning strategy. Create new yearly archive partitions for completed years. Verify that RangeStart still correctly covers your oldest data. Consider whether your DQ window is still appropriately sized given changes in user query patterns.
You now understand the full architecture of Power BI Large Format Datasets with hybrid tables — from the storage engine mechanics that make partition-level query routing possible, to the operational patterns that keep a production enterprise model healthy.
The key mental model to carry forward: a hybrid table is not a compromise between Import and DirectQuery. It's an architectural pattern that uses each mode for exactly the workloads it's suited for. VertiPaq handles what it does best — compressed, lightning-fast scans of stable historical data. DirectQuery handles what it does best — always-current reads of a bounded recent window. The query planner connects them transparently.
The implementation discipline that separates successful deployments from troubled ones is partition boundary management. Every problem we examined in the troubleshooting section traces back to either a boundary misalignment, an oversized DQ window, or a measure that doesn't respect partition boundaries in its filter context.
For your next steps:
Explore composite model aggregations on top of your hybrid table. Aggregations let you pre-build month-level and year-level summaries that answer executive-level queries without scanning detailed fact partitions at all.
Implement deployment pipelines for your LFD dataset. Power BI Deployment Pipelines support XMLA-enabled models, allowing you to promote model changes from Dev to Test to Production without republishing from Desktop.
Study the Premium capacity metrics app. Install the Premium Capacity Metrics app from AppSource and connect it to your capacity. The memory and CPU usage breakdowns per dataset are essential for right-sizing your Premium SKU as your model grows.
Learn TMSL/XMLA scripting for partition management at scale. As your model grows to dozens of large partitions, manual Tabular Editor operations become impractical. TMSL scripts deployed via Azure DevOps pipelines give you version-controlled, automated partition lifecycle management.
The hybrid table architecture you've built today is the foundation that your entire enterprise reporting layer can run on — not a workaround, not a prototype, but a production-grade solution to a genuinely hard problem.
Learning Path: Enterprise Power BI