A semantic model is composite when its tables use different storage modes, or when its DirectQuery tables come from different data sources. Composite models let you extend a shared model with your own data, mix import speed with live data, or join several sources. Microsoft's own advice is to prefer Import mode wherever possible, because composite models can be slower and can give surprising numbers.
When to use one
Microsoft names three good reasons:
- Speed: a DirectQuery model needs faster queries, so you set storage per table and add user-defined aggregations.
- Extra data: you need to add imported data or calculated tables to a DirectQuery model.
- Several sources: you need to combine two or more DirectQuery sources, which can be databases or tabular models.
A model that connects to one tabular model and adds nothing is not composite. It is a DirectQuery model with a single source group, often used only to rename fields, change sort order or set formats.
Which kinds you can build, and where
| Kind of composite model | Where you can build it |
|---|---|
| DirectQuery to another Power BI semantic model, with or without extra import or DirectQuery tables | Power BI Desktop only |
| DirectQuery tables from different data sources | Desktop and the service |
| Import and DirectQuery tables | Desktop and the service |
| Import and Direct Lake tables | Web modeling only |
| DirectQuery and Direct Lake tables | XMLA only |
In composite models, Direct Lake means Direct Lake on OneLake (Lesson 4). Direct Lake on SQL endpoint cannot be added.
The five storage modes
Set them per table in the Properties pane under Advanced > Storage mode. The Desktop status bar shows Mixed when a file has both import and DirectQuery tables.
| Mode | Best use |
|---|---|
| DirectQuery | Very large or near-real-time fact tables. Nothing is imported |
| Import | Tables that do not filter or group DirectQuery or hybrid fact tables, and sources DirectQuery cannot read. Calculated tables are always Import |
| Dual | Dimension tables queried together with DirectQuery fact tables from the same source. A dual table acts as both import and DirectQuery |
| Hybrid | A fact table with import partitions plus one DirectQuery partition: cached history plus real-time latest data |
| Direct Lake | Delta tables in OneLake (Lesson 4) |
Keep the refresh schedule of dual and hybrid tables in step with the source, so the cached copy does not drift.
The key idea: source groups and limited relationships
- Each DirectQuery source is its own source group. All imported data, including Direct Lake, forms one group together.
- A relationship inside one group is regular (unless it is many-to-many, which makes it limited).
- A relationship across groups is always limited. It has no guaranteed "one" side and behaves like an inner join. Rows with no match on both sides disappear.
- Cross-group relationships default to many-to-many whatever the real cardinality is. Set the correct one yourself.
- Items from a DirectQuery group are remote. Measures and calculated tables you define in the model are local.
A small example. An Orders table (import) has 2024: 40 and 2025: 60. A Budget table from another source has 2024: 50, 2025: 70 and 2026: 90. A visual by year shows only 2024 and 2025, because the limited relationship drops 2026, which has no orders. The total budget still shows 210, since the date filter does not apply to the budget side. Both numbers are "right" and they look inconsistent. Test for this before you publish.
Query paths: why dual mode helps
- Import or dual tables only: answered from the cache. Fastest.
- Dual or DirectQuery tables from the same source: sent to the source as one native query, with regular relationships.
- Dual or hybrid tables from the same source: the cache serves the import partitions and the source serves the rest.
- Anything else: crosses source groups. Relationships are limited, and groupings from non-DirectQuery tables are sent as virtual tables, which is slow when the groupings are large.
Rules for cross-group relationships
| Guideline | Value |
|---|---|
| Unique values in a number key | About 50,000 or fewer |
| Text keys | Keep cardinality times average text length small. The two Microsoft pages differ (250,000 and 1,000,000 characters), so aim for the lower one |
| Date relationships | Relate on a higher grain, such as a month key instead of a date key |
| Dimensions and facts | Keep them in the same source group whenever possible |
| Report design | Build each visual from one source group and place visuals from other groups side by side |
Why the date example matters: a date table from 1900 to 2100 has 73,414 rows. Related across groups, it makes Power BI write very long filter lists. Move the date table into the same group or use a month key.
Other performance facts from Microsoft: a query that must aggregate locally at a lower grain fails above 1 million cardinality. DistinctCount across sources sends one query per group-by value. DirectQuery visuals should refresh in 5 seconds or less.
To avoid a cross-group relationship altogether, add a duplicate dimension table to each group and link a visible slicer and a hidden slicer with the same sync group name.
User-defined aggregations
- Add them to DirectQuery tables to answer high-level queries from a small table.
- An aggregation table should have at least 10 times fewer rows than its base table. A 1-billion-row table needs an aggregation of 100 million rows or fewer.
- Import aggregations make the model composite.
- Hybrid tables do not support aggregations, and you cannot add them to a single DirectQuery partition.
Step by step: extend a shared enterprise model
- Open a blank file in Power BI Desktop and connect to the shared Power BI semantic model.
- Choose Make changes to this model so you can add your own tables.
- Use Get data to add your own table, such as a budget from Excel. Desktop warns you that the model is now composite.
- Create the relationship on a low-cardinality key and set its cardinality correctly (the default is many-to-many).
- Build visuals from one source group where you can.
- Test every
CALCULATEthat uses a remote measure. Writing a formula with the measure and writing it with the rawSUMcan return different results, because the filter travels along different paths. - Publish, then test as a normal viewer, not as the owner.
Chaining rules for models built on other models
- The longest allowed chain is three models.
- Sign-in uses single sign-on. Service principal sign-in is not supported.
- All models must be in the same tenant. Required tenant switches: XMLA endpoint access, live connections to semantic models in Excel, and DirectQuery connections to semantic models.
- Owners need Build permission on the sources. Viewers need Read on the composite model and on every model in the chain.
- KPIs, row-level security and translations are not imported from the source. You cannot edit source relationships or change the data type of a loaded column (use a calculated column).
- Do not rename semantic models or workspaces. Composite models connect by workspace and model name, so a rename can break them.
- After the source schema changes, refresh the composite model in Desktop, republish and retest. Use lineage and impact analysis in the service first.
The design pattern Microsoft recommends: publish the enterprise model as a reliable foundation and let others extend it. Do not make the single version of the truth itself a composite model.
Security: what carries over and what does not
- Row-level security applies only on the source where it is defined. You cannot define it on a table from another group, or on a local table related to another group.
- If Model C uses a table from Model A, a report user on Model C can query any Model A table that is not protected by row-level security.
- Object-level security is defined and applied only on the source model. A composite model copies the schema as its author could see it, so consumers see objects based on the author's access. Changes apply after the composite model refreshes.
A query sent to one source can carry values from another, such as names from an Excel file inside a SQL query. Anyone who can read traces or audit logs may see them, so check encryption on every source.
Watch-outs
- Calculated tables are always imported. A calculated table based on a DirectQuery table shows values as of its last refresh.
- Calculated columns on relational DirectQuery sources allow only row-level expressions, with no iterators and no
CALCULATE. Remote tabular models allow all DAX. Chained models allow neither calculated columns nor calculated tables. - Calculation groups on remote sources are not supported and results are undefined. A measure that adds a remote and a local value can pick up a remote group's logic on only one side.
- Incremental refresh works only for SQL, Oracle and Teradata sources.
- Not usable as DirectQuery sources: SQL Server Analysis Services before 2022, multidimensional models, SAP HANA, SAP BW, real-time and sample semantic models, and models in My workspace. Streaming semantic models are not supported.
- Publish to web is not supported for models with a DirectQuery connection to another semantic model.
- With row-level security and DirectQuery tables together, new source rows appear only after the next model refresh.
- Turning a live connection into a local DirectQuery model stops visual caching being shared across reports. This can slow reports and raise load on the service.
- When you edit a composite model over XMLA, update the
ChangedPropertiesandPBI_RemovedChildrencollections, or Power BI tools may overwrite your changes.