Composite Models in Power BI – A Complete Step-by-Step Guide

Composite Models in Power BI allow you to combine data from multiple sources and create efficient, scalable, and high-performance reports. With Composite Models, you can:

✅ Mix DirectQuery and Import modes in the same dataset.
✅ Blend multiple DirectQuery sources, including Power BI datasets and Azure Analysis Services models.
✅ Enable hybrid data connectivity to balance performance and real-time updates.
✅ Enhance flexibility in complex data models by reducing the dependency on a single data source.

This guide will explain Composite Models in Power BI, their benefits, how to set them up, and best practices to optimize performance. 🚀

---

🔹 What is a Composite Model in Power BI?

A Composite Model allows Power BI users to work with multiple storage modes within a single dataset. This means you can:

1️⃣ Import data for fast performance (Import Mode).
2️⃣ Use DirectQuery for real-time updates (DirectQuery Mode).
3️⃣ Combine multiple DirectQuery sources, including Power BI datasets.

Previously, Power BI did not allow mixing Import and DirectQuery in the same model, but Composite Models overcome this limitation.

---

🔹 Storage Modes in Power BI

Before using Composite Models, it’s important to understand the three storage modes in Power BI:

Storage Mode	Description	Pros	Cons
Import	Data is fully loaded into Power BI	Fastest performance, supports all DAX functions	Not real-time, larger memory consumption
DirectQuery	Queries data directly from the source	Real-time data, reduced memory usage	Slower performance, limited DAX functionality
Dual Mode	Tables can switch between Import and DirectQuery	Best of both worlds, flexibility	Complex relationships

💡 Dual Mode tables act as either Import or DirectQuery, improving performance while maintaining flexibility.

---

🔹 Step 1: Enable Composite Models in Power BI

1. Open Power BI Desktop.
2. Click “File” → “Options and settings” → “Options”.
3. Under “Global” → “Preview features”, enable “DirectQuery for PBI datasets and AS”.
4. Restart Power BI for the changes to take effect.

💡 This step is required to allow Power BI to combine multiple DirectQuery sources.

---

🔹 Step 2: Connect to Multiple Data Sources

You can connect to multiple data sources in DirectQuery, Import, or a mix of both.

➡️ Connect to an Import Data Source

1. Click “Home” → “Get Data”.
2. Select “SQL Server,” “Excel,” “SharePoint,” etc.
3. Click “Import” to load data into Power BI.

➡️ Connect to a DirectQuery Data Source

1. Click “Get Data” → Select “SQL Server”.
2. In the connection settings, choose DirectQuery instead of Import.
3. Click OK to establish the connection.

💡 Now, your model contains both Import and DirectQuery tables, forming a Composite Model.

---

🔹 Step 3: Managing Relationships in Composite Models

Power BI automatically creates relationships between tables, but you should manually adjust them for better performance.

➡️ Steps to Manage Relationships

1. Open Model View.
2. Drag and drop fields between tables to create relationships.
3. Click on the relationship line to edit properties.
4. Set Cardinality (One-to-Many or Many-to-Many).
5. Choose Cross-filter direction (Single or Both).

💡 Tip: Be cautious with Many-to-Many relationships, as they may impact performance.

---

🔹 Step 4: Using Dual Mode for Performance Optimization

By default, Import and DirectQuery tables do not communicate efficiently. To optimize performance, set key tables to Dual Mode.

➡️ Steps to Change a Table to Dual Mode

1. Go to Model View.
2. Select a DirectQuery table.
3. In the Properties pane, change the Storage Mode to Dual.

💡 Dual tables act as Import for faster queries but switch to DirectQuery when needed for real-time updates.

---

🔹 Step 5: Creating Measures in Composite Models

With Composite Models, you can create DAX measures to aggregate and analyze data across multiple sources.

➡️ Example 1: Total Sales from Both Import and DirectQuery Tables

Total Sales = SUM(ImportTable[Sales]) + SUM(DirectQueryTable[Sales])

➡️ Example 2: Dynamic YOY Growth

YOY Growth = 
VAR CurrentYearSales = SUM(ImportTable[Sales])
VAR PreviousYearSales = CALCULATE(SUM(ImportTable[Sales]), SAMEPERIODLASTYEAR(DateTable[Date]))
RETURN DIVIDE(CurrentYearSales - PreviousYearSales, PreviousYearSales, 0)

💡 DAX calculations in Composite Models work across multiple sources, but some functions (e.g., calculated tables) may be limited in DirectQuery.

---

🔹 Step 6: Building Reports with Composite Models

Now that the Composite Model is ready, build visualizations in Report View.

➡️ Steps to Create a Report

1. Drag a date field from DateTable to the X-axis of a Line Chart.
2. Add Total Sales to the Y-axis.
3. Use Slicers to filter by Region, Product, or Date.

💡 Reports update in real-time when using DirectQuery sources.

---

🔹 Step 7: Publish and Share the Composite Model

Once the report is ready, publish it to Power BI Service.

➡️ Steps to Publish the Report

1. Click “File” → “Publish”.
2. Select a Power BI workspace.
3. Share the report with your team.

💡 Composite Models require Gateway Configuration for DirectQuery connections to on-premises sources.

---

🔹 Best Practices for Composite Models

✅ Use Dual Mode for frequently used tables to improve performance.
✅ Avoid Many-to-Many relationships, as they can slow down queries.
✅ Optimize DirectQuery tables by reducing unnecessary columns.
✅ Minimize calculated columns and tables in DirectQuery mode.
✅ Use aggregations to speed up queries in DirectQuery mode.

---

🔹 Summary of Composite Models in Power BI

✅ Enabled Composite Models in Power BI
✅ Connected Import and DirectQuery data sources
✅ Created relationships between tables
✅ Optimized performance using Dual Mode
✅ Built measures using DAX
✅ Created interactive reports
✅ Published and shared the report in Power BI Service

Composite Models enhance flexibility and scalability, making Power BI more powerful for enterprise reporting and real-time analytics. 🚀

Would you like a sample Power BI file for Composite Models?