Power BI Composite Models: Mixing Import and DirectQuery

A composite model in Power BI is a dataset that contains more than one storage mode — for example, some tables stored in imported (in-memory) mode and others connected via DirectQuery to a live source. This enables a common and powerful pattern: historical data imported for fast query performance, while current-day or operational data is read live from the source.

This guide covers how composite models work, when to use them, the configuration options, and the limitations that constrain their use.

Why composite models exist

The two fundamental storage modes in Power BI have inverse strengths:

**Import mode**: Data is loaded into Power BI's in-memory VertiPaq engine. Queries are fast — milliseconds, regardless of source complexity. But the data is only as fresh as the last scheduled refresh, which is typically once per hour at best, often daily.

**DirectQuery**: Every filter interaction sends a live SQL query to the source. Data is always current. But performance depends entirely on the source database's query speed — a complex DAX query translated to SQL against a large database may take 10–30 seconds.

Most analytical use cases need both: fast historical analysis plus current-day visibility. Before composite models, this required choosing one mode for the entire dataset — accepting either stale data (import) or slow performance (DirectQuery). Composite models remove this trade-off.

How composite models work

In a composite model, each table in the semantic model is assigned a storage mode:

**Import**: The table's data is loaded into VertiPaq memory on refresh. Queries against this table are fast.

**DirectQuery**: The table's data is not imported. Queries read live from the source.

**Dual**: The table is imported AND available for DirectQuery. Power BI chooses which mode to use based on the query context — if the query can be satisfied from imported data, it uses import; if the query crosses a relationship to a DirectQuery table, it uses DirectQuery. Dual mode is appropriate for dimension tables in composite models, where the dimension is used both in import-only queries and in queries that cross to DirectQuery fact tables.

When a DAX query crosses a relationship between an imported table and a DirectQuery table, Power BI sends a SQL query to the DirectQuery source and merges the result with the imported data in memory. This is called a "mixed source query."

Common use case: historical import + current DirectQuery

The most common composite model pattern:

- Historical fact table (2+ years): Import mode. Loaded nightly. Fast for trend analysis, period comparison, and year-over-year metrics.

- Current-day operational table: DirectQuery to a live operational database or data warehouse. Always fresh. Used for today's sales, today's inventory, current order status.

- Shared dimension tables: Dual mode. Calendar, Customer, Product dimensions are imported (fast) but can also be queried in DirectQuery context.

This composite model enables a dashboard that shows "Year-to-date revenue vs prior year" (fast, from import) alongside "Orders placed in the last hour" (live, from DirectQuery) without separate datasets or report pages.

Multi-source composite models

Composite models can combine tables from completely different data sources. A single Power BI dataset can have:

- Imported tables from Snowflake

- DirectQuery tables from Azure Synapse

- Imported tables from a SharePoint list

This is the "multi-source" capability. It allows combining data that lives in different systems without a centralised ETL that merges everything before it reaches Power BI. The trade-off: cross-source relationships have restrictions — certain relationship types are not supported across sources, and query performance on cross-source joins can be unpredictable.

Limitations

**Limited relationships across sources**: Between an import table (from source A) and a DirectQuery table (from a different source B), only single-directional relationships are supported. Bidirectional cross-filtering across sources is not supported. This limits some DAX patterns.

**Aggregate tables for performance**: A DirectQuery table with millions of rows will produce slow queries if the BI layer is doing complex aggregations at query time. Power BI's aggregation tables (user-defined pre-aggregated import-mode tables) can accelerate DirectQuery performance for common aggregations. Configure an aggregation table that summarises the DirectQuery fact table by commonly queried dimensions; Power BI routes qualifying queries to the aggregation table automatically.

**Refresh coordination**: In a composite model where imported tables come from one source and DirectQuery from another, refresh schedules are independent. The imported tables refresh on a schedule; the DirectQuery tables are always live. If the two sources are not in sync (the warehouse hasn't loaded today's data yet), the dashboard will show inconsistent numbers — historical data from the import source and current data from the live source that doesn't yet include today's transactions.

**Row-level security complexity**: RLS in composite models requires careful configuration. RLS defined on a DirectQuery table is pushed down to the source database as a filter. RLS defined on an import table is applied in VertiPaq. For cross-source queries, both RLS policies must be compatible.

Configuring storage mode

In Power BI Desktop, open the Model view. Select a table → Properties pane → Storage Mode. Choose Import, DirectQuery, or Dual.

When you change a table from Import to DirectQuery, Power BI warns that the change is irreversible for that table (it removes the imported data). Plan storage mode assignments before loading significant data.

For the broader Power BI semantic model context, see power bi row-level security and power bi performance optimization. Our Tableau consulting practice covers Power BI architecture alongside Tableau — book a scoping call to discuss your multi-source analytics requirements.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.