You've got a classic problem: your organization's operational database is churning out sales transactions in real time, and your executives want dashboards that reflect what happened in the last 15 minutes. But they also want those same dashboards to show rolling 24-month trend lines, regional performance benchmarks, and year-over-year comparisons that require pulling from a data warehouse with hundreds of millions of rows. If you solve for the real-time requirement, you end up with DirectQuery, and your historical aggregations become painfully slow. If you solve for performance, you import everything, and your "live" data is hours old by the time anyone looks at it.
This is exactly the problem Power BI Composite Models were built to solve. Composite Models let you combine multiple storage modes — Import, DirectQuery, and Dual — within a single Power BI dataset. You can pin your transactional data to a DirectQuery connection that always reflects the latest state, while your historical fact tables and slowly-changing dimension data live in memory as imported tables, giving you sub-second query response times where it matters most. The result is a model that's genuinely real-time where it needs to be and blazing fast where it doesn't.
By the end of this lesson, you'll understand not just how to configure Composite Models, but why the underlying architecture works the way it does — and how to make smart decisions about which tables belong in which storage mode based on your data characteristics, refresh patterns, and query patterns.
What you'll learn:
Before diving in, you should have solid working experience with Power BI Desktop, including creating data models with multiple tables and writing DAX measures. You should understand the difference between Import and DirectQuery mode at a conceptual level — if you've built reports with both independently, you're in good shape. Familiarity with Power Query (M language) is helpful but not strictly required for this lesson. Basic SQL knowledge will help you understand the DirectQuery query generation examples we'll walk through.
Before you start flipping storage mode toggles in Power BI Desktop, you need to understand what the engine is doing when you query a Composite Model. This isn't just academic — understanding the mechanics will directly inform every design decision you make.
When you submit a DAX query to a Composite Model (either through a report visual or through external tools like DAX Studio), Power BI's formula engine decomposes that query and routes different pieces of it to different execution engines. Imported table data goes to the VertiPaq in-memory columnar engine, which is extraordinarily fast — we're talking microseconds to milliseconds for most operations. DirectQuery table data generates SQL (or the native query language of the source) and sends it across a network connection to your source system, which means latency is now bounded by your network, your source system's query performance, and concurrency limits.
Here's where it gets interesting: when a single visual requires data from both an imported table and a DirectQuery table, the formula engine has to orchestrate the results. It will often query the DirectQuery source with a SQL statement that may already incorporate a filter derived from your imported table, then join or blend the results in memory. This is called a cross-source join, and it's the most performance-sensitive operation you'll encounter in Composite Models.
Consider a scenario where you have an imported DimProduct table (50,000 rows, fully loaded into VertiPaq) and a DirectQuery FactSalesLive table pointing to your operational SQL Server database. When a user filters a chart by product category, Power BI will:
ProductKey values matching the selected category filterIN clause filter to the SQL query sent to your DirectQuery sourceThis means your DirectQuery queries are almost always more efficient in a Composite Model than in a pure DirectQuery model, because the VertiPaq engine can pre-filter data before the SQL is generated. But it also means that if your filter from an imported table produces a very large list of values (say, 50,000 product keys), that SQL IN clause can become unwieldy, and some source systems will actually fail or time out on it.
Architectural insight: Power BI has a configurable limit on how many values it will pass in an
INclause. You can observe the actual SQL being generated by enabling DirectQuery mode in DAX Studio and examining query traces. This is invaluable for diagnosing performance problems.
Let's walk through each storage mode with enough depth to make real decisions.
Import mode copies data from your source into the VertiPaq in-memory columnar store. The data is compressed using a combination of run-length encoding and dictionary encoding, which is why a 10GB CSV file might only occupy 1.5GB of memory in the model. Queries against imported tables are consistently fast regardless of source system performance or network conditions, because by query time, the source system is completely out of the picture.
The limitations are well-known: your data is only as fresh as your last refresh, and large datasets require significant memory. But there's a subtler limitation in the context of Composite Models that's worth calling out: relationships that cross from DirectQuery to Import tables have directionality constraints. We'll cover this in detail shortly.
DirectQuery mode stores no data in the model. Every query that touches a DirectQuery table generates a native query (usually SQL) sent live to the source. The data is always current, limited only by source system latency.
The performance implications are real. DirectQuery is not slow by nature — a well-designed query against a properly indexed SQL Server table can return in under 100ms. But every visual on a page that touches a DirectQuery table represents at least one round-trip to the source, and if you have 10 visuals on a page, you may be generating 10-40 SQL queries simultaneously. This is why DirectQuery reports typically need more careful visual design than Import reports.
There are also semantic limitations in DirectQuery. Some DAX functions are not supported in DirectQuery mode because they can't be translated into SQL. Functions like TOPN with complex ordering, certain time-intelligence functions, and user-defined calculated tables are restricted.
Dual mode is where things get genuinely clever, and it's underused because it's poorly understood. A table set to Dual mode is simultaneously imported and available as a DirectQuery passthrough. The engine decides at query time which strategy to use based on the context.
Here's why this matters: dimension tables are usually small and relatively static — think date dimensions, product hierarchies, customer segments. When they're used in a context that only involves other imported tables, VertiPaq uses the cached import. When they're used in a context that involves DirectQuery tables — like when you're filtering a live fact table — the engine may choose to pass the dimension query through to the source as well, which can produce more efficient join behavior at the database level rather than requiring the IN clause approach described earlier.
The practical upshot: set your dimension tables to Dual mode whenever those dimensions exist in both your import sources and your DirectQuery sources, or when they participate in relationships with DirectQuery fact tables. This gives the optimizer maximum flexibility.
To set storage modes in Power BI Desktop, open the Model view (select the grid icon in the left sidebar), right-click on a table, and choose "Properties." The Storage Mode dropdown will be visible in the Properties pane on the right. You can also select multiple tables, right-click, and set them all at once — helpful when configuring a model with many dimension tables.
Warning: Changing a table from Import or Dual back to DirectQuery after it's been set will trigger a warning that cached data will be deleted. This is not reversible without a refresh. Plan your storage mode strategy before you start loading data.
Let's ground this in a real scenario. You're building a sales intelligence platform for a retail chain with 400 stores. Your architecture looks like this:
FactSalesLive — rolling 90 days of transactions, updated every 5 minutes. ~15 million rows at any given time.FactSalesHistory — 5 years of historical sales, ~800 million rows. Also contains DimProduct, DimStore, DimDate, DimCustomer.You want dashboards that show both real-time today's performance and historical context. Here's how you'd approach the model design.
Not everything needs to be live. Your historical fact table doesn't change — once a day is closed, those records are immutable. Your dimension tables change slowly — a product's category might change quarterly. Your operational fact table changes constantly.
This gives you an initial storage mode assignment:
| Table | Source | Storage Mode | Rationale |
|---|---|---|---|
| FactSalesLive | Azure SQL (Operational) | DirectQuery | Must be current |
| FactSalesHistory | Synapse | Import + Incremental Refresh | Immutable, 800M rows need aggregation |
| DimProduct | Synapse | Dual | Used with both fact tables |
| DimStore | Synapse | Dual | Used with both fact tables |
| DimDate | Calculated or Synapse | Import | Pure lookup, never DirectQuery |
| DimCustomer | Synapse | Dual | Used with both fact tables |
In Power BI Desktop, you'll establish multiple data source connections. Use Get Data to connect to your Azure SQL Database for the operational tables. Use a separate Get Data connection to Azure Synapse Analytics for your warehouse tables. Power BI will track each table's source connection independently.
When you add a DirectQuery table from your operational SQL Database while other tables already exist from Synapse, Power BI will ask whether you want to "Add a local model" if you're connecting to a Power BI dataset, or it will simply allow the connection in a file-based model. In Desktop, multiple source connections in one model are simply supported — you don't need to explicitly declare that you're creating a Composite Model. It becomes one automatically when you mix storage modes or sources.
This is where a lot of practitioners hit walls. Relationships between tables in different storage modes have specific rules, and understanding them prevents a lot of frustrating error messages.
The fundamental constraint: A relationship between a DirectQuery table and an Import table can only filter in the direction from the Import table to the DirectQuery table, not the other way around — unless you explicitly enable bidirectional filtering, which carries significant performance risks.
In practical terms: your DimProduct (Import/Dual) can filter FactSalesLive (DirectQuery), which is exactly what you want. But FactSalesLive filtering DimProduct to produce calculated columns or measures requires bidirectional cross-filtering, which you should think carefully about before enabling.
To create the relationship, go to the Model view and drag ProductKey from DimProduct to ProductKey in FactSalesLive. The relationship dialog will appear. Confirm the cardinality (many-to-one from fact to dimension), and leave cross-filter direction as "Single" unless you have a specific reason for bidirectional.
Performance warning: Bidirectional relationships involving DirectQuery tables can produce an explosion in the number of SQL queries generated. A single visual with multiple bidirectional cross-filter paths can result in dozens of SQL round-trips. Enable it only when genuinely necessary, and always test performance under concurrent load.
Here's a nuanced scenario: what if FactSalesLive is in Azure SQL Database and you also have a FactInventoryLive table in a different DirectQuery source, say a Cosmos DB or a different SQL Server instance? Can you create a relationship between them?
The answer is: not a native in-engine relationship, because Power BI can't push a cross-source join down to either database. Instead, you'll need to:
CROSSFILTER or TREATAS to handle cross-source filtering manuallyLimited relationships are Power BI's way of saying "I know these tables are conceptually related, but I can't guarantee referential integrity and I can't generate efficient cross-source joins." They still function — visuals will work — but you lose some guarantees about filter propagation, and you need to be more deliberate in your DAX.
// Example: Sales measure that explicitly handles cross-source filtering
Total Live Sales =
CALCULATE(
SUM(FactSalesLive[SalesAmount]),
TREATAS(
VALUES(DimStore[StoreKey]),
FactSalesLive[StoreKey]
)
)
This TREATAS pattern treats the current filter on DimStore[StoreKey] as if it were a filter directly on FactSalesLive[StoreKey], bypassing relationship propagation and making the filter explicit. It's more verbose, but it's predictable.
Even with smart storage mode assignments, an 800-million-row FactSalesHistory table imported into Power BI would require an enormous amount of memory and a very long refresh time. This is where aggregation tables — one of the most powerful and least-used features in Power BI — become essential.
An aggregation table is a pre-summarized version of a detail fact table. Instead of storing 800 million individual transaction rows, you store 36 months × 400 stores × 50,000 products = potentially a few million pre-aggregated rows. For most business queries, this aggregated data is sufficient. For the rare drill-down case where someone needs invoice-level detail, Power BI transparently falls back to the detail table.
First, you'll create a pre-aggregated version of your data in Power Query. Connect to Synapse and write a query that aggregates at the grain you need:
-- Query used as the source for your aggregation table in Power Query
SELECT
DateKey,
StoreKey,
ProductKey,
SUM(SalesAmount) AS TotalSalesAmount,
SUM(Quantity) AS TotalQuantity,
COUNT(*) AS TransactionCount
FROM FactSalesHistory
GROUP BY DateKey, StoreKey, ProductKey
Load this as a table called FactSalesHistory_Agg in Import mode. Then, in the Model view, also connect to FactSalesHistory as a DirectQuery table (the detail grain). You'll have both tables in the model.
With FactSalesHistory_Agg selected in the Model view, find the "Manage aggregations" option in the table's context menu (or through the Table Tools ribbon). This opens the aggregation configuration dialog.
For each column in the aggregation table, you map it to the detail table:
| Aggregation Column | Summarization | Detail Table | Detail Column |
|---|---|---|---|
| TotalSalesAmount | Sum | FactSalesHistory | SalesAmount |
| TotalQuantity | Sum | FactSalesHistory | Quantity |
| TransactionCount | Count table rows | FactSalesHistory | (table-level) |
| DateKey | Group By | FactSalesHistory | DateKey |
| StoreKey | Group By | FactSalesHistory | StoreKey |
| ProductKey | Group By | FactSalesHistory | ProductKey |
Once configured, Power BI will automatically use FactSalesHistory_Agg for queries that can be satisfied at the aggregated grain. If a user drills down to a transaction-level detail, it falls back to FactSalesHistory via DirectQuery. This fallback is completely transparent — you don't write special DAX for it.
Design principle: Your aggregation table grain should match the most common query grain in your reports. If 90% of your queries are at the month-store level, aggregate there. Don't over-aggregate (losing drill-down capability) or under-aggregate (reducing the performance benefit).
Use DAX Studio to check whether your queries are hitting the aggregation table or falling back to the detail. Connect to your Power BI Desktop model from DAX Studio, enable "Server Timings," and run your visual's underlying DAX query. In the Server Timings panel, you'll see whether the storage engine used VertiPaq (aggregation hit) or issued a DirectQuery call (fallback). A well-designed aggregation table should have a 90%+ hit rate for dashboard-level visuals.
For the FactSalesHistory table — which grows by the day but never changes historically — importing all 800 million rows is wasteful. You don't need to re-import the 2019 data every morning when only yesterday's records are new. Incremental refresh solves this.
In Power Query, define two parameters named exactly RangeStart and RangeEnd, both of type Date/Time. This naming convention is required — Power BI uses these parameter names to inject the refresh range automatically.
Then, filter your FactSalesHistory query using these parameters:
// In Power Query (M language)
let
Source = Sql.Database("yoursynapse.sql.azuresynapse.net", "YourDatabase"),
FactSalesHistory = Source{[Schema="dbo", Item="FactSalesHistory"]}[Data],
FilteredRows = Table.SelectRows(
FactSalesHistory,
each [TransactionDate] >= RangeStart and [TransactionDate] < RangeEnd
)
in
FilteredRows
This date filter must be applied to a datetime column that Power BI can fold into the query — meaning the filter must be translated into SQL and executed at the source, not in Power Query in memory. Query folding is non-negotiable for incremental refresh to work correctly. Verify it by right-clicking the final step in Power Query and checking whether "View Native Query" is available. If it is, folding is happening.
Then, right-click FactSalesHistory in the Fields pane and select "Incremental refresh." Configure it to store data for 5 years and refresh data for the last 3 days (to account for late-arriving transactions or corrections).
Critical note: Incremental refresh in conjunction with a Composite Model is a powerful pattern, but it requires your dataset to be published to the Power BI Service with Premium capacity or Premium Per User licensing. The incremental refresh partitioning is managed by the service. In Power BI Desktop, you always see all data via the full query — the partitioning only takes effect after publishing.
Writing DAX in a Composite Model requires you to be more explicit about what data you're operating on. Some patterns that work fine in pure Import models behave differently when DirectQuery tables are involved.
Certain DAX functions cannot be translated to SQL and will either fail or cause the engine to pull all rows from the DirectQuery source into memory before operating on them — a "fallback" that can be catastrophic for large tables. Functions to avoid on DirectQuery tables include:
RANKX with complex expressionsTOPN with non-trivial ordering on DirectQuery columnsPATH and related hierarchy functionsSUMMARIZE with added columns on DirectQuery tablesInstead, push as much computation as possible to the source (as views or stored procedures), and use DAX only for the final business logic layer.
A common mistake in Composite Models is using FILTER with a row-by-row expression on a DirectQuery table inside a CALCULATE:
// BAD: This iterates every row of FactSalesLive in memory
Filtered Live Sales (Slow) =
CALCULATE(
SUM(FactSalesLive[SalesAmount]),
FILTER(FactSalesLive, FactSalesLive[SalesAmount] > 1000)
)
This forces the engine to fetch all rows from FactSalesLive into memory before applying the filter. Instead, use column-level filters that can be pushed down to SQL:
// GOOD: This becomes a WHERE clause in the generated SQL
Filtered Live Sales (Fast) =
CALCULATE(
SUM(FactSalesLive[SalesAmount]),
FactSalesLive[SalesAmount] > 1000
)
The second version generates SQL like WHERE SalesAmount > 1000, which executes at the database and returns only the filtered result set.
One of the most powerful things you can do in a Composite Model is write DAX that seamlessly combines live and historical data. Here's a practical example — a measure that shows today's running total against the same day's historical average:
Today vs Historical Average =
VAR TodaySales =
CALCULATE(
SUM(FactSalesLive[SalesAmount]),
DimDate[DateKey] = TODAY()
)
VAR HistoricalAvgSameDay =
CALCULATE(
AVERAGEX(
VALUES(DimDate[DateKey]),
CALCULATE(SUM(FactSalesHistory[SalesAmount]))
),
DimDate[DayOfWeek] = WEEKDAY(TODAY()),
DimDate[DateKey] < TODAY() - 1
)
RETURN
DIVIDE(TodaySales, HistoricalAvgSameDay) - 1
This measure queries FactSalesLive (DirectQuery, real-time) for today's number and FactSalesHistory (Import with aggregations) for the historical baseline. The formula engine handles the routing automatically. The result is a live performance-vs-historical comparison that would have required two separate datasets and complex report-level calculations in the pre-Composite Models era.
When you combine data sources in a Composite Model, security gets layered — and the layers can interact in ways that surprise you.
Row-Level Security (RLS) rules are defined at the model level and applied by the Power BI engine regardless of storage mode. However, for DirectQuery tables, RLS filters translate into additional WHERE clauses appended to the generated SQL. This is important for two reasons:
First, it works — your RLS rules will correctly filter DirectQuery data. Second, the SQL credentials used for the DirectQuery connection must have access to the underlying data, and your RLS rules layer on top. This means a DirectQuery data source must be configured with credentials that have broad access to the table, and Power BI enforces row-level restrictions at the semantic model layer, not the database layer. If someone with database access queries the source directly, they bypass Power BI's RLS entirely.
For highly sensitive data, this is a meaningful architectural consideration. If you need database-level security (not just semantic model-level), you'll need to implement views or row-level security at the database level as well.
When you publish a Composite Model to the Power BI Service, each DirectQuery connection must have configured credentials in the dataset settings. If you're connecting to Azure SQL Database, you'll likely use OAuth2 or SQL authentication. If you're connecting to an on-premises source, you'll need an on-premises data gateway.
A common gotcha: if two DirectQuery tables come from different servers but you want to use the same credentials, you still need to configure them separately in the dataset settings. Power BI tracks credentials per data source connection.
Security best practice: Use service accounts with least-privilege access for DirectQuery connections. The service account should have read-only access to only the tables required by the model. Broad database-level read access increases the blast radius if credentials are ever compromised.
You've built your Composite Model, published it to the service, and your users are complaining that some pages load slowly. Here's how to diagnose and fix it systematically.
The Performance Analyzer (under the View ribbon) records the time each visual takes to render, broken into DAX query time and visual rendering time. For DirectQuery visuals, the DAX query time will include the round-trip to the source. Enable it, clear the cache (there's a button in the Performance Analyzer panel), and interact with your report to capture timings.
Any visual showing DAX query times over 2-3 seconds is worth investigating. Click "Copy query" to extract the DAX generated by that visual, then analyze it in DAX Studio.
Connect DAX Studio to your published Power BI dataset (or to the Desktop instance via localhost). Key diagnostics:
Server Timings tab: Shows how long the formula engine spent vs. the storage engine. High formula engine time suggests the DAX is complex. High storage engine time with DirectQuery callouts indicates the SQL queries are the bottleneck.
Query Plan: Shows the logical and physical query plan. Look for SpoolLookupIterator operations, which indicate that the formula engine is iterating row-by-row — often the culprit in slow measures.
DirectQuery SQL: For DirectQuery tables, DAX Studio can show you the exact SQL that Power BI generated. Compare this to queries you'd write by hand and check for missing index coverage.
In Power BI Desktop, the Options dialog (under Report Settings) has a section called "Query reduction." These settings reduce the number of queries sent to DirectQuery sources during user interaction:
For reports with significant DirectQuery content, both of these settings are almost always worth enabling. The user experience trade-off (waiting for an Apply button vs. instant filtering) is almost always acceptable when the alternative is a 15-second page load.
If your DirectQuery sources are on-premises and accessed via a data gateway, the gateway becomes a critical performance component. The gateway runs on Windows and mediates all DirectQuery traffic. For high-concurrency dashboards (many simultaneous users), a single-node gateway can become a bottleneck. Power BI supports gateway clusters — deploy multiple gateway nodes behind a single logical gateway, and Power BI will load-balance queries across them.
As a rough guideline: for every 50 concurrent report users generating DirectQuery traffic, plan for at least one dedicated gateway node with 8+ CPU cores and 32GB+ RAM. Monitor gateway CPU and memory usage in the gateway management portal.
Now it's time to build this yourself. This exercise walks you through creating a small Composite Model that demonstrates all the key concepts.
Scenario: You work for a regional restaurant chain. You have two data sources:
LiveOrders table (today's orders, updated in real time)HistoricalOrders data (past 2 years, already aggregated by day and location)You'll also use a DimLocation table and a DimDate table.
Step 1: Set up your data sources
Create a SQL Server table (or use a CSV if you don't have SQL Server access) with these columns: OrderID, LocationID, MenuItemID, OrderAmount, OrderTimestamp. Populate it with about 1,000 rows of today's date.
Create an Excel file with a sheet named HistoricalOrders with columns: DateKey, LocationID, TotalOrderAmount, OrderCount. Populate it with 730 rows (2 years × 365 days). Add a DimLocation sheet with LocationID, LocationName, Region, City.
Step 2: Connect and assign storage modes
Open Power BI Desktop. Use Get Data to connect to your SQL Server and import the LiveOrders table. When connecting, choose DirectQuery mode. Then use Get Data again to connect to your Excel file and load HistoricalOrders, DimLocation, and a date table (or create one with CALENDARAUTO()).
Set DimLocation to Dual mode. Set DimDate to Import mode. HistoricalOrders should be Import. LiveOrders should be DirectQuery.
Step 3: Create relationships
In Model view, create the following relationships:
DimLocation[LocationID] → LiveOrders[LocationID] (one-to-many)DimLocation[LocationID] → HistoricalOrders[LocationID] (one-to-many)DimDate[DateKey] → HistoricalOrders[DateKey] (one-to-many)Note: You won't be able to create a native date relationship to LiveOrders for the OrderTimestamp column without a calculated column, because calculated columns are not supported on DirectQuery tables. Instead, create a measure that extracts the date from OrderTimestamp for filtering purposes.
Step 4: Write the comparison measure
Create a measure that blends both sources:
Today Revenue vs 30-Day Avg =
VAR TodayRevenue =
CALCULATE(
SUM(LiveOrders[OrderAmount]),
DATESINPERIOD(DimDate[Date], TODAY(), 0, DAY)
)
VAR Last30DayAvg =
CALCULATE(
AVERAGEX(
VALUES(DimDate[DateKey]),
CALCULATE(SUM(HistoricalOrders[TotalOrderAmount]))
),
DATESINPERIOD(DimDate[Date], TODAY() - 1, -30, DAY)
)
RETURN
IF(
ISBLANK(Last30DayAvg),
BLANK(),
DIVIDE(TodayRevenue - Last30DayAvg, Last30DayAvg)
)
Step 5: Enable Performance Analyzer
Add a card visual showing Today Revenue vs 30-Day Avg, enable Performance Analyzer, and capture the query timings. Observe which part of the query touches DirectQuery vs. VertiPaq. Change the location slicer and watch the timings change.
Step 6: Experiment with query reduction
Go to File → Options → Current File → Report Settings and enable the Apply button for slicers. Notice how the report behavior changes. Open Performance Analyzer again and compare the query firing pattern.
If your dimension tables are Import but your fact tables are DirectQuery, every filter from a dimension to a DirectQuery fact must go through the IN clause mechanism. With large dimension tables, this can generate enormous IN lists. Setting dimensions to Dual gives the optimizer the option to push dimension queries through to the source as JOINs, which is usually more efficient.
Many-to-many relationships in Power BI are internally implemented as bidirectional filter chains. When one or both sides of the many-to-many is a DirectQuery table, this generates complex SQL with nested subqueries that can time out or produce unexpected results. Avoid many-to-many relationships with DirectQuery tables wherever possible. Instead, create a bridge table in Import mode that handles the resolution.
Calculated tables in Power BI are materialized at model load time using DAX. If a calculated table's DAX expression references a DirectQuery table, Power BI will attempt to pull all rows of that DirectQuery table into memory to evaluate the calculation — completely defeating the purpose of DirectQuery. Calculated tables should only reference Import or Dual mode tables.
// This will fetch ALL rows of FactSalesLive into memory
// DO NOT DO THIS
RecentProductSales =
SUMMARIZE(
FactSalesLive, // DirectQuery table
FactSalesLive[ProductKey],
"TotalSales", SUM(FactSalesLive[SalesAmount])
)
If your Power Query transformations break query folding (for example, by using a custom function or a non-foldable transformation before the date filter), incremental refresh will silently fail to partition correctly. The model will still "work," but it will re-import all data on every refresh instead of just the recent window. Always verify folding by checking "View Native Query" at the final step of your Power Query chain.
Composite Models with DirectQuery components can look great in single-user testing and fall apart under production load. Your SQL Server might handle 5 concurrent queries fine but struggle with 50. Before going live, use the Power BI Premium load testing tools or simply ask 20 colleagues to open the report simultaneously. Watch your source system's performance metrics.
This error means a DAX function in one of your measures cannot be translated to SQL and cannot be evaluated in the DirectQuery context. Review your measure and identify any iterating functions (SUMX, AVERAGEX, RANKX, etc.) that reference DirectQuery table columns directly. Either move the iterating logic to an Import table, pre-aggregate at the source, or rewrite the measure to avoid the unsupported function.
If a measure that blends Import and DirectQuery data produces incorrect totals, the most likely cause is filter propagation through a limited relationship. Check the relationship between the tables in Model view — if it shows a dashed line, it's a limited relationship and filters may not propagate as expected. Use TREATAS in your DAX to explicitly pass filter context across limited relationships.
No architecture pattern is universally correct. There are situations where Composite Models will create more problems than they solve.
When your DirectQuery source can't handle the query volume: If your source database is already running at 80% CPU for its primary application workload, adding Power BI DirectQuery on top will create production incidents. In this case, replicate to a read replica or use a different storage mechanism entirely.
When data freshness requirements are truly sub-minute: Power BI's DirectQuery isn't a streaming solution. If you need second-level freshness displayed in real time, look at Power BI's streaming datasets or integrate with Azure Event Hubs via the streaming API. Composite Models with DirectQuery are appropriate for "last 5 to 15 minutes" freshness, not sub-minute.
When all data fits in memory and refreshes quickly: If your entire dataset — including "historical" data — is 10GB or less and can refresh in under an hour, a well-designed Import model will outperform a Composite Model in every dimension. Don't add architectural complexity without a genuine need.
When your DirectQuery source doesn't support efficient aggregate queries: Some legacy databases produce poor execution plans for the types of aggregate GROUP BY queries that Power BI generates. If every DirectQuery query takes 30+ seconds because the source can't optimize it, the solution is to fix the source (add indexes, create summary views) or not use DirectQuery — not to build an elaborate Composite Model on top of a fundamentally slow query engine.
Composite Models represent a genuine architectural advancement in Power BI's maturity as an enterprise analytics platform. When designed carefully, they let you solve the fundamental tension between data freshness and query performance — not by compromising on either, but by applying the right storage strategy to each piece of data based on its actual characteristics.
The mental model to take away: think about data in terms of its volatility and its query grain. Highly volatile data that must be current belongs in DirectQuery. Stable historical data at fine grain belongs in Import with incremental refresh. Everything in between — your slowly-changing dimensions, your summary aggregations — can use Dual mode or materialized aggregation tables to serve as a bridge.
Here's what to build next to deepen your mastery:
Explore aggregation tables with user-defined aggregations: We covered the managed aggregation feature, but Power BI also supports manual aggregation tables where you have full control over the aggregation grain and structure. This is valuable when your data has complex hierarchical aggregation requirements.
Implement XMLA endpoint access for your Composite Model: If your organization uses Premium capacity, the XMLA endpoint lets you connect Tabular Editor, SQL Server Management Studio, or custom applications to your published model. You can push schema changes, automate partition management, and monitor query activity with far more depth than the standard Power BI Service UI allows.
Study the VertiPaq Analyzer output: VertiPaq Analyzer (a free tool that works with DAX Studio) shows you the memory footprint of every table and column in your Import model segments. Understanding which columns consume the most memory, and why, leads directly to better column encoding decisions and smaller, faster models.
Learn Tabular Object Model (TOM) scripting: For production Composite Models with complex incremental refresh partitioning, manual partition management via TOM scripts gives you precision control over exactly which data ranges are refreshed, at what schedule, and with what priority. This is the foundation of enterprise-grade data engineering on the Power BI platform.
The best way to truly internalize Composite Models is to build one with your real data — not toy datasets. Take a report you've already built in pure Import mode, identify the table that's most time-sensitive, and migrate it to DirectQuery while leaving the rest as Import. Watch what happens to the model behavior, measure the query performance, and develop your own intuition for where the seams are. That real-world friction is where expert understanding gets built.
Learning Path: Getting Started with Power BI