Power BI Composite Models - When and How to Use Them

Here's a scenario I run into constantly with Australian enterprise clients. They've got a properly governed data warehouse in SQL Server or Azure Synapse. Finance owns it, IT manages it, and it contains years of sales data. But then a department head has an Excel spreadsheet with product manager assignments, or budget targets, or regional forecasts that aren't in the warehouse. And they want one report that brings it all together.

Before composite models, this was a painful choice. Either import everything (and lose the real-time connection to the warehouse), or use DirectQuery for everything (and lose the ability to add local data). Neither option was great.

Composite models solve this properly. They let you mix Import and DirectQuery storage modes in the same Power BI semantic model. Your warehouse data stays in DirectQuery so it's always current. Your Excel data gets imported. And Power BI handles the joins between them. Simple concept, but the implications for how organisations build reports are significant.

What Makes a Model "Composite"

A Power BI semantic model becomes composite when it contains tables with different storage modes. That's the formal definition. In practice, there are a few patterns:

Import + DirectQuery - The most common scenario. Some tables are imported into the model (cached in Power BI), while others connect live to a data source via DirectQuery. This is what you use when you want to combine a large live data source with smaller reference data.

DirectQuery to multiple sources - Your model has DirectQuery connections to different databases. Maybe sales data comes from SQL Server and inventory data comes from a separate PostgreSQL database. Both stay live, but they're in the same model.

DirectQuery to Power BI semantic models - This one changed how we think about enterprise BI architecture. You can create a DirectQuery connection to another published Power BI semantic model and then extend it with your own local tables. This enables a layered approach where a central team maintains the core model and departments add their own context on top.

Direct Lake + Import - For organisations using Microsoft Fabric, you can combine Direct Lake tables with imported data through web modelling.

Each combination has its own tooling requirements and limitations, which I'll get into below.

Building a Composite Model - A Real Example

Let me walk through the most common scenario we build for clients.

You've got an enterprise data warehouse with sales data. Millions of rows, refreshed throughout the day. You connect to it using DirectQuery because importing that volume isn't practical and you need current data.

Now your product team has a spreadsheet mapping products to product managers. Maybe 200 rows. They want to see sales by product manager.

Getting this data into the warehouse would take weeks, maybe months. There's a change request process, someone needs to design the table, ETL needs to be built, it needs testing. Meanwhile the product team needs this report for a board presentation next Tuesday.

With a composite model:

1. Open Power BI Desktop
2. Connect to the data warehouse using DirectQuery - your sales tables are now in DirectQuery storage mode
3. Click "Get data" and select your Excel file - the product manager table gets imported
4. Create a relationship between the Bike table (from DirectQuery) and the ProductManagers table (from Import)
5. Build your visual showing Sales Amount by Product Manager

Done. The sales data stays live from the warehouse. The product manager mapping is imported. Power BI handles the cross-source join.

The relationship between these tables defaults to many-to-many cardinality because they come from different sources. You can change the cardinality to match your actual data (one-to-many, many-to-one, etc.), but the behaviour of cross-source relationships is different from same-source relationships. More on that below.

Source Groups - Understanding the Architecture

This is where it gets a bit more technical, but understanding source groups is important if you're going to build composite models that perform well.

Every composite model has source groups. A source group is a collection of tables and relationships from the same source. All imported tables (regardless of whether they came from Excel, CSV, or another database) belong to one source group. Each DirectQuery connection creates its own source group.

Why does this matter? Because relationships between tables in the same source group behave differently from relationships between tables in different source groups.

Intra source group relationships are "regular" relationships. Power BI can push the query logic down to the source and get optimised results. These behave exactly like relationships in a non-composite model.

Cross source group relationships are "limited" relationships. Power BI has to retrieve data from both sources separately and join them locally. You can't use DAX functions to look up values from the "one" side of a many-to-one relationship across source groups. And performance depends on the cardinality of the join columns.

The practical advice: keep your cross-source relationships on low-cardinality columns. Ideally under 50,000 distinct values. And avoid large string columns as join keys between source groups - if you must use strings, the total string length (cardinality multiplied by average string length) should stay below 250,000.

Building on Other Power BI Semantic Models

This is the pattern I'm most excited about, and it's where composite models really change how enterprise BI works.

Imagine your central BI team publishes a curated semantic model with sales, customer, and product data. It's governed, validated, and trusted. Now a regional team wants to build reports that combine this data with their own local forecasts.

With composite models, they can:

1. Create a DirectQuery connection to the published semantic model
2. Click "Make changes to this model" to create a local model
3. Add their own imported tables (forecasts, targets, regional data)
4. Create relationships between the enterprise data and their local data
5. Add measures and calculated columns
6. Publish their composite model

The enterprise data stays live through DirectQuery. The regional team gets their custom view without touching the source model. If the enterprise model is updated, those changes flow through automatically.

You can even chain these models - up to three levels deep. The central model connects to Analysis Services. A department builds on top of that. A team builds on top of the department model. Each level can add its own context without disrupting the levels above.

The permissions model is worth understanding here. The owner of each composite model needs Build permission on any semantic models they connect to. Users viewing reports need Read permission on every model in the chain. If any model in the chain is in a Premium Per User workspace, viewers need a Premium Per User licence.

Security Considerations You Can't Ignore

Composite models introduce data leakage risks that you need to think about carefully.

When Power BI sends a query to your data warehouse, the query might include values from your imported data. In our product manager example, the SQL query sent to the warehouse includes the product manager names from the spreadsheet - embedded in WHERE clauses. Anyone who can view query logs or audit trails on the warehouse can see that data, even if they don't have permission to the original spreadsheet.

This matters when the imported data is confidential. Salary information, performance ratings, strategic plans - think twice before combining sensitive imported data with DirectQuery sources where other people can inspect queries.

Also consider encryption boundaries. If your warehouse connection uses encryption but your imported data source doesn't (or vice versa), data from one source might end up in unencrypted queries to the other. Make sure both sides of the connection use appropriate encryption.

For models built on other Power BI semantic models, row-level security (RLS) applies at the source model where it's defined. It does not cascade to composite models built on top. If the source model has RLS protecting certain rows, those rules apply when DirectQuery retrieves the data. But you can't define RLS on a table loaded from a remote source, and RLS defined on local tables won't filter remote tables.

Performance - Being Honest About the Trade-offs

Composite models are not free performance-wise. Every cross-source relationship involves Power BI pulling data from both sides and joining locally. The performance impact depends on the scenario.

Small reference tables joined to large DirectQuery tables - This is the sweet spot. Performance is usually fine. The imported table is small, so the filter values passed to the DirectQuery source are manageable.

Large join cardinalities - When the join column has thousands of distinct values, Power BI might need to pass all of them to the DirectQuery source in a WHERE clause. This can be slow, and if the cardinality exceeds a million, the query will fail outright.

DistinctCount across sources - This is the worst case. If you're doing a distinct count on a column from one source grouped by a column from another source, Power BI might need to send a separate query per group value. This can mean hundreds or thousands of individual queries to your data source.

My advice: always test with realistic data volumes. A composite model that works perfectly with 100 rows of imported data might struggle with 10,000.

Practical Recommendations

Use dual storage mode when possible. If your Import and DirectQuery tables come from the same data source, you can set tables to "Dual" storage mode. This caches data locally for joins but still queries the source for visuals. It gives you the performance of Import for relationships without losing the freshness of DirectQuery.

Don't import what you can leave in DirectQuery. It's tempting to import "just a few more tables" for performance. Resist this unless you actually have performance problems. Every imported table is data you need to refresh, and refresh failures can break reports.

Start small with chaining. The three-level chain limit exists for good reason. Each level adds latency and complexity. Start with one level of composition and only add more if there's a clear business need.

Document your source groups. When someone else inherits your composite model six months from now, they need to understand which tables are imported, which are DirectQuery, and how the cross-source relationships work. Power BI Desktop's relationship view shows this, but adding documentation helps.

If your organisation is building out a Power BI analytics strategy and composite models are on the table, our Power BI consulting team can help you design the right architecture. We work with Australian businesses across industries to build business intelligence solutions that scale properly and don't paint you into a corner.

For organisations using Microsoft Fabric alongside Power BI, we also offer Microsoft Fabric consulting to help you take advantage of Direct Lake and the broader Fabric data platform.

The full Microsoft documentation on composite models is available at Use composite models in Power BI Desktop.