How to Enable Composite Model in Power BI

Wrestling with Power BI's data modes can feel like being stuck between a rock and a hard place. Do you use Import mode for lightning-fast performance but have to deal with stale data between refreshes? Or do you choose DirectQuery for real-time data but pay the price with slower report performance and query limitations? Thankfully, you don't have to choose. This article will show you how to use Power BI's Composite Model feature to get the best of both worlds, combining different data storage modes in a single report for maximum flexibility and performance.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is a Composite Model in Power BI?

A composite model is a feature in Power BI Desktop that allows a single report to seamlessly connect to different types of data sources. More importantly, it lets you combine different storage modes — like DirectQuery and Import — within the very same data model. This means you can create a hybrid model tailored to your exact needs.

Before composite models, you had to commit your entire report to one of two main storage modes:

• Import: This is the default and most common mode. Power BI imports a copy of your data from the source and stores it within the .PBIX file. Queries are incredibly fast because they are hitting this in-memory cache. The big drawback is that the data is only as fresh as your last refresh schedule.
• DirectQuery: In this mode, no data is actually stored in the Power BI file. Instead, Power BI sends queries directly to the underlying data source every time a user interacts with a visual. This is great for real-time reporting on very large datasets, but it can be slow and comes with limitations in DAX and transformations.

A composite model breaks down these barriers. You could, for example, use DirectQuery to connect to a massive, multi-billion row sales database for up-to-the-minute transaction data while simultaneously importing a small, static Excel file that contains your sales team's regional targets. The composite model lets you relate these two tables together as if they lived in the same place.

Key Benefits of Using a Composite Model

Mixing storage modes might sound complex, but the benefits make it a powerful tool for any serious Power BI user. It allows you to build more efficient, flexible, and powerful reports.

Optimize Report Performance

This is the biggest advantage. You can keep your largest, most frequently updated tables (like a sales or event log table) in DirectQuery mode to avoid lengthy data refreshes and an inflated file size. At the same time, you can import smaller dimension tables (like product lists, customer details, or date calendars) for faster slicing and dicing. Visuals that only use imported tables will be lightning fast, while visuals tapping into the live data can still provide real-time insights.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Unify Disparate Data Sources

Composite models let you combine vastly different data sources. Imagine building a single dashboard that pulls:

• Real-time ERP data from an on-premise SQL Server (via DirectQuery).
• Website traffic data from Google Analytics (via an imported connector).
• Quarterly budget data from an Excel file stored in SharePoint (via Import).
• Customer data from Salesforce (via another imported connector).

You can create relationships between all these sources to get a true 360-degree view of your business performance without having to build a complex data warehouse first.

Work with Massive Datasets

Sometimes you need to analyze datasets that are simply too large to import into Power BI. We're talking terabytes of data. DirectQuery is the only option here, but it traditionally limits your modeling capabilities. A composite model allows you to connect to this massive dataset via DirectQuery while enriching it with smaller, imported helper tables to make your analysis more meaningful.

How to Create a Composite Model: A Step-by-Step Guide

Let's walk through building a simple composite model. Our goal is to analyze real-time sales data from a SQL Server database and compare it against sales targets stored in a basic Excel spreadsheet.

Step 1: Connect to Your DirectQuery Source

First, we'll connect to our main data source, which contains our large, frequently changing sales data. We want this to be a live connection.

1. In Power BI Desktop, go to Get data > SQL Server.
2. Enter your server and database information.
3. Critically, under Data Connectivity mode, select DirectQuery and click OK.
4. Navigate to and select your sales fact table (e.g., FactSales) and any related dimension tables you want to keep in DirectQuery mode (e.g., DimDate, DimProduct). Click Load.

When you go to the Model view in Power BI, you'll see your tables. Hovering over a table's header will show you its storage mode is "DirectQuery." You'll also notice a colored bar along the top of each table card indicating its mode.

Step 2: Connect to Your Import Source

Now, let's bring in our Excel file with sales targets. Power BI is smart enough to recognize a mix of modes is happening and will start creating a composite model automatically.

1. Go to Get data > Excel workbook.
2. Locate your Excel file and open it.
3. Select the worksheet containing your targets (e.g., SalesTargets) and click Load.

Once loaded, head back to the Model view. You'll see your new SalesTargets table. If you hover over its header, you'll see its storage mode is "Import." Power BI has now officially created a composite model!

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Understanding Storage Modes for Tables

With a composite model active, you gain granular control over each table's storage mode. Click on any table in the Model View, and then look at the Properties pane. Under the Advanced section, you will see a dropdown for Storage mode. Here you have three options:

• Import: The table's data is fully loaded into Power BI's memory. Fastest for queries.
• DirectQuery: The table remains a direct connection to the source. No data is stored.
• Dual: This is a special mode, only available in composite models. A table in Dual mode can act as either Import or DirectQuery depending on the context of the query. Power BI will choose the most efficient path. If you filter visuals using only columns from tables in Import or Dual mode, Power BI pulls from the cache. If a query requires data from a purely DirectQuery table, it pushes the query to the source database.

Step 4: Create Relationships Between Different Modes

The real magic happens when you connect tables across different storage modes. Let's create a relationship between our FactSales (DirectQuery) table and our SalesTargets (Import) table, connecting them via a common column like Region or Date.

Simply drag the connecting column from one table to the other, just as you normally would. Power BI will create the relationship, allowing you to build visuals that use data from both tables at the same time.

Note: You might see a warning about potential performance implications. This is normal. Power BI is letting you know that queries combining these sources can be more complex and potentially slower, as they may require joining data from the in-memory cache with data from the remote source.

Best Practices for Composite Models

Building composite models gives you great power, but it also comes with a few things to keep in mind to ensure your reports remain fast and reliable.

Use Dual Mode Strategically

So, when should you use Dual mode? The best candidates for Dual mode are your common dimension tables. Think of your Date or Product tables.

By setting a dimension table to Dual, you allow it to serve queries efficiently for both your huge DirectQuery fact table and any other imported fact tables you might have. This avoids potential bottlenecks and minimizes the complex cross-source queries Power BI has to perform behind the scenes.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Pay Attention to Relationship Integrity

Relationships between a DirectQuery table and an imported table are known as "limited relationships" (sometimes also referred to as "weak relationships" in earlier versions). This is because Power BI cannot guarantee one-to-one referential integrity between an in-memory table and a live database connection.

In practice, this means some DAX functions may behave differently, and performance can sometimes be affected. For standard filtering and visuals, it usually works flawlessly, but be mindful of this when writing complex calculations.

Think About Aggregations

To take performance to the next level, composite models are often paired with another powerful feature: aggregations. You can create an imported aggregation table that contains a pre-summarized version of your massive DirectQuery table. Power BI will be smart enough to hit this super-fast imported aggregation for high-level visuals (like yearly sales by country) and only send a live query to the DirectQuery source when a user drills down to a granular level (like individual transaction details).

Final Thoughts

Power BI's composite models are a game-changer, removing the firm barrier between Import and DirectQuery. By thoughtfully combining storage modes, you can build reports that are both performant and provide access to vast amounts of real-time data, giving you a level of flexibility previously reserved for enterprise-grade BI systems.

While mastering composite models is an incredibly valuable skill for any data analyst, it highlights the inherent complexity in traditional BI tools. We built Graphed because we believe getting insights shouldn't require you to become an expert in storage modes, data modeling, or query performance tuning. By connecting your sources like Google Analytics, Shopify, and Salesforce directly, we let you build real-time dashboards and ask questions in plain English, while we handle the underlying complexity of your data automatically.