What Is a Composite Model in Power BI?
A composite model is a Power BI dataset where some tables are loaded into memory using import mode while other tables query the data source live using DirectQuery, all combined in a single model with relationships between them. This lets you import small, stable dimension tables for speed while keeping large or fast-changing fact tables in DirectQuery for freshness. It is the standard approach when a single storage mode cannot satisfy both performance and data currency requirements.
A composite model in Power BI combines import mode and DirectQuery within a single dataset, letting you mix fast, cached tables with tables that query the source live. It solves the problem of needing both speed and freshness in the same report.
Why You Would Mix Storage Modes
Import mode and DirectQuery each solve a different problem, and most real reporting needs touch both at once. Import mode compresses and caches data inside Power BI, giving very fast query response but only as current as the last scheduled refresh. DirectQuery sends a live query to the source every time a report loads, keeping numbers current to the second but tying performance directly to the source database. A composite model exists because many real datasets have both kinds of tables: reference data like product catalogues or customer lists that barely change and benefit from being imported, alongside transactional data that changes constantly and needs to stay live.
How Composite Models Are Built
You build a composite model by setting the storage mode individually per table inside a single Power BI dataset. In Power BI Desktop, each table’s properties include a storage mode setting: Import, DirectQuery, or Dual. Relationships between tables work as normal regardless of which mode each side uses, though Power BI applies certain rules to avoid ambiguous query plans, particularly around how filters propagate across mixed-mode relationships. The result, from the report author’s perspective, looks like one ordinary dataset; the storage mode differences are invisible in the report canvas and only matter for performance and refresh behaviour.
Dual Mode: the Middle Ground
Dual mode is the option that makes composite models genuinely useful rather than just a workaround. A table set to Dual can act as either imported or DirectQuery depending on context: when a query only needs data from imported tables, Power BI uses the cached copy for speed; when a query also touches a DirectQuery fact table, Power BI can use the DirectQuery version of the dual table to keep the join consistent with live data. This is most commonly applied to dimension tables like date, customer, or product tables that need to join cleanly with both an imported summary table and a DirectQuery detail table in the same model, without maintaining two separate copies.
Common Use Cases
The clearest use case is combining a large, frequently updated transactional source, such as an ERP or CRM’s live order table, with smaller supporting tables that rarely change, like a currency exchange rate table or a store location list. Another common pattern involves aggregation tables: import a pre-summarised, fast-loading aggregate table for high-level dashboard visuals, while keeping the full-detail table in DirectQuery so users can drill down to individual transactions when needed. This aggregation-plus-detail pattern, sometimes called “aggregations” in Power BI documentation, is one of the most effective ways to get both dashboard-level speed and transaction-level detail from the same dataset.
Trade-offs to Watch
Composite models add real complexity compared to a single-mode dataset. Query performance on visuals that span both imported and DirectQuery tables can be harder to predict and tune, since Power BI has to translate between its in-memory engine and native queries against the source. Refresh behaviour also differs: DirectQuery tables do not need scheduled refresh in the same way import tables do, which can be confusing for report consumers used to a single refresh schedule. Before adopting a composite model, confirm the performance gain or freshness requirement genuinely justifies the added design and maintenance complexity, and consider whether Premium capacity is needed to handle the additional query load comfortably.
Our tip: Start any composite model design by identifying which single table absolutely must stay live, then design everything else around importing as much as possible. Every table you can safely import instead of leaving in DirectQuery reduces load on your source database and makes the report noticeably more responsive for end users.
Frequently asked questions
When should I use a composite model instead of pure import mode?
Use a composite model when at least one table in your report is too large or changes too frequently to import comfortably, but you also have smaller reference tables that benefit from import mode's speed. A common pattern is importing dimension tables like customers or products while leaving a large, constantly updated transactions table in DirectQuery. If all your data fits comfortably in import mode and does not need to be live, a pure import model is simpler and usually performs better.
Does a composite model slow down my whole report?
Only the DirectQuery tables are affected by source database performance; queries that touch only imported tables run at import-mode speed. However, when a visual combines both an imported table and a DirectQuery table, Power BI has to coordinate between the in-memory engine and a live query, which adds overhead. Performance depends heavily on how well the source database is indexed and how the relationships between imported and DirectQuery tables are designed.
What is a dual storage mode table?
Dual mode is a third option available specifically in composite models, where a table can behave as either imported or DirectQuery depending on which other tables a given query touches. It is most often used for dimension tables that need to join efficiently with both imported and DirectQuery fact tables in the same model, avoiding the need to duplicate the dimension table twice.
Do composite models require Power BI Premium?
No, composite models work with Power BI Pro licensing; they are a modelling feature, not a licensing tier. That said, organisations using composite models at scale often also use Premium capacity because DirectQuery workloads and larger combined models benefit from dedicated capacity's higher query limits and performance guarantees.