What is a Composite Model in Power BI?

Building a Power BI report often means bringing together data from different places, like a massive sales database and a simple Excel spreadsheet for marketing budgets. A composite model is Power BI’s clever way of letting you combine these different types of data sources - some live and real-time, others imported for speed - all within a single report. This article will walk you through what composite models are, why they’re so useful, and how to start using them.

Graphed

Still Building Reports Manually?

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

Watch the Demo

First, The Basics: Import vs. DirectQuery

To understand what makes a composite model special, you first need to know the two primary ways Power BI connects to data: Import mode and DirectQuery mode.

Import Mode: The "Snapshot" Method

Import mode is the most common way to get data into Power BI. When you use Import, Power BI takes a copy of your data from the source (like an Excel file, CRM, or database) and stores it inside the Power BI file (.pbix) itself. Think of it like taking a snapshot.

• What happens: The data is physically loaded into Power BI's high-performance memory engine. It’s compressed and optimized for speed.
• Pros: Reports are incredibly fast. Because the data is stored in the Power BI file, visuals load almost instantly. You also have access to the full range of DAX functions without limitations.
• Cons: The data is only as fresh as your last refresh. If your source data updates every minute, your report won't see those changes until you hit the refresh button or run a scheduled refresh. There are also data size limits, typically around 1 GB per dataset for a Pro account.

Best for: Smaller datasets that don't change constantly, like monthly sales targets, product lookup tables, or historical marketing campaign data.

DirectQuery Mode: The "Live Connection" Method

DirectQuery mode works differently. Instead of copying the data, Power BI leaves the data right where it lives in the original source database (like a SQL Server, Snowflake, or Google BigQuery). It simply stores a connection to it.

• What happens: Every time you interact with a visual in your report - like clicking a filter or slicing a chart - Power BI sends a query directly to the source database to get the latest data.
• Pros: Your reports are always showing real-time data. There's no need to schedule refreshes for data freshness. It's also perfect for enormous datasets that are too large to import into Power BI.
• Cons: Performance depends heavily on the speed of your source database. Slow database equals slow report. Because queries are translated on the fly, some DAX functions are not supported or behave differently.

Best for: Very large datasets (billions of rows) or situations where you absolutely need real-time data, such as a factory C-suite dashboard or a live inventory tracker.

Graphed

Still Building Reports Manually?

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

Watch the Demo

So, What is a Composite Model?

A composite model is a hybrid approach - it lets you use both Import and DirectQuery mode tables in the same Power BI model. For a long time, you had to choose one or the other for your entire report. Composite models broke down that wall, giving you the best of both worlds.

Imagine you run an e-commerce store. You have a massive sales transactions table in your company’s SQL database with millions of new sales being added every hour. This is a perfect candidate for DirectQuery because you need live sales data and the table is too big to import.

At the same time, you have small, simple "lookup" tables, like a list of your products, sales territories, or a simple date table. These tables hardly ever change. It makes no sense to constantly re-query a database for this static information. So, you can set these tables to Import mode for maximum speed.

A composite model allows you to do just that: your massive Sales table is in DirectQuery mode, while your smaller Product and Calendar tables are in Import mode, all working together in one report.

Why and When You Should Use a Composite Model

Composite models solve some very common business intelligence challenges. Here are the most practical scenarios where they are a lifesaver.

1. Optimizing Performance on Large Datasets

This is the most common use case. By keeping your huge, frequently updated "fact tables" (like sales transactions, web logs, or sensor readings) in DirectQuery, you avoid a massive import process. Meanwhile, you can set your smaller "dimension tables" (like Customers, Products, Locations) to Import. Queries that only involve dimension tables are lightning-fast because they use Power BI's internal engine, significantly improving the user experience of slicing and dicing.

Graphed

Still Building Reports Manually?

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

Watch the Demo

2. Combining Enterprise Data with Local Data

Your team doesn't always live entirely inside the corporate data warehouse. Maybe IT manages the main sales database in SQL Server (which you connect to via DirectQuery), but your marketing team tracks campaign results and budgets in a simple Excel file on SharePoint.

With a composite model, you can connect to the SQL Server in DirectQuery mode and, in the same report, import the Excel file. You can then build relationships between the Sales from your database and Marketing Spend from your Excel sheet to calculate things like Return on Investment (ROI) across both sources.

3. Enriching Real-Time Reports with Historical Data

Suppose you have a report that needs to show live operational data, like current staffing levels or active user sessions, through DirectQuery. You can enrich this real-time view by importing static historical benchmarks or targets from another source. This allows you to compare live performance against your business goals without slowing down the core data connection.

Creating a Composite Model and The 'Dual' Storage Mode

Creating a composite model happens automatically the moment you mix connection types. Here’s a high-level view of how it works:

1. You connect to your first data source. For instance, a SQL Server database, and you choose DirectQuery when prompted. All tables from this source will be in DirectQuery mode.
2. Next, you go to "Get Data" again but this time connect to an Excel file. Excel files can only be imported, so Power BI pulls that data in via Import mode.

That's it. Power BI will display a message telling you that you've created a composite model with potential security implications (since data may travel between sources). Your model now contains a mix of DirectQuery and Import tables.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Understanding 'Dual' Storage Mode

When you have a composite model, you also unlock a third storage mode option for your dimension tables: Dual.

Think of Dual as a smart, flexible storage mode. A table in Dual mode can act as either Import or DirectQuery depending on what the visual needs at that moment.

• If a query only needs a table from the Import "side" (e.g., Products) and another in Dual mode (e.g., Calendar), Power BI will treat the Calendar table as Import for maximum speed.
• If a query needs to involve the Sales_Transactions (DirectQuery) and the Calendar table (Dual), Power BI will switch Calendar to act as a DirectQuery source to satisfy the query.

By setting a dimension table to Dual mode, you enable Power BI to choose the most efficient path for each query, offering the best of both worlds from a performance standpoint.

Potential Pitfalls and Considerations

While powerful, composite models introduce some complexity you should be aware of.

• Model Complexity: It's suddenly very important to know which table is in which mode. In Power BI's Model view, tables are color-coded: blue headers for DirectQuery, plain for Import, and blue-dashed for Dual. Keeping track of this is essential for troubleshooting performance issues.
• Performance Isn't Magic: While you can optimize with Import and Dual tables, any visual that hits your DirectQuery table is still only as fast as your source database. A slow source is still a slow source.
• Many-to-Many Relationships: Composite models allow for more flexible relationships between tables, including many-to-many. While this can solve certain modeling problems, they often come with performance downsides and can return unexpected results if you're not careful.

Final Thoughts

Power BI composite models give you incredible flexibility by blending fast, imported data with live, real-time connections in a single, unified report. By strategically mixing Import, DirectQuery, and Dual storage modes, you can build powerful reports that are both performant and scalable, navigating the trade-offs between data freshness and speed.

As you've seen, managing these different storage modes, data connections, and performance considerations can get complicated quickly. Before you spend weeks watching tutorials and troubleshooting model relationships, you might find that you can get the answers you need in a much simpler way. With a tool like Graphed , we handle the messy parts of data integration for you. We connect directly to your sources - like Shopify, Google Analytics, and CRMs - and let you create real-time dashboards just by describing what you want to see. Instead of a full-time job managing a data model, you can just ask questions and get instant insights.