Composite models in Power BI - Step-by-Step Guide

Introduction

Composite models let you combine data from different sources in one Power BI report. This helps you analyze data together without moving or copying it all into one place.

When you want to combine data from a cloud source and a local file in one report.

When your sales data is in a database but customer info is in an Excel file and you want to analyze both.

When you need to add a small table to a big dataset without importing the whole big dataset.

When you want to use DirectQuery for live data and import mode for static data in the same report.

When you want to improve report performance by mixing import and DirectQuery data.

Steps

Step 1: Open Power BI Desktop

- Start screen

Power BI Desktop opens with a blank report

Step 2: Click Get data

- Home tab

Get Data window appears showing data source options

Step 3: Select a data source that supports DirectQuery (e.g., SQL Server) and connect

- Get Data window

Data loads in DirectQuery mode and appears in Fields pane

Step 4: Click Get data again

- Home tab

Get Data window appears again

Step 5: Select another data source (e.g., Excel file) and import data

- Get Data window

Imported data appears in Fields pane alongside DirectQuery data

Step 6: Create relationships between tables from different sources

- Model view

Tables from different sources connect and can be used together in visuals

Step 7: Build visuals using fields from both DirectQuery and imported tables

- Report view

Visuals show combined data from multiple sources

Before vs After

Before

Report has only one data source, either imported or DirectQuery, limiting combined analysis

After

Report has multiple data sources combined, allowing analysis across imported and live data

Settings Reference

Storage mode

📍 Model view > Table properties

Choose how data is stored and accessed for each table

Default: Import

Enable composite models

📍 Options > Preview features

Turn on support for composite models in Power BI Desktop

Default: Disabled

Relationships active/inactive

📍 Model view > Relationship line

Control which relationships are used in calculations

Default: Active

Common Mistakes

Trying to create relationships between tables without enabling composite models

Power BI blocks relationships between DirectQuery and imported tables if composite models are off

Enable composite models in Preview features before combining data sources

Using too many DirectQuery tables causing slow report performance

DirectQuery queries live data each time, which can slow down visuals

Use import mode for static or small tables and DirectQuery only for large or frequently updated data

Summary

Composite models let you mix imported and live data in one Power BI report.

You can create relationships between tables from different sources for combined analysis.

Remember to enable composite models in settings and choose storage modes wisely for best performance.