Lessons
Introduction to BI and Power BI
DAX Time Intelligence and Related Functions Self-Service BI Power BI Career Roles Qlik Sense vs Power BI Power BI Editions Power BI Features Power BI vs Tableau Power BI Use Cases What Is Power BI Data Driven Decision Making BI Tools What is Business Intelligence?
Power BI Data Modeling
Hierarchy in Power BI Composite Model in Power BI Cross Filter Direction in Power BI Many-to-Many Relationship in Power BI Relationships in Power BI Data Modelling in Power BI
Power BI DAX
DAX Logical Functions MCQS DAX RANKX And Drill Through DAX Variables DAX CALCULATE Function DAX Text Functions DAX Logical Functions DAX Functions List DAX Formulas DAX Syntax DAX Calculated Table DAX Fundamentals
Power Query

Composite Model in Power BI

Last Updated: 03 Sep, 2026

What Is a Composite Model in Power BI?

A composite model in Power BI combines tables using different storage modes — Import and DirectQuery — in a single report. This lets you keep large, frequently updated tables in DirectQuery (queried live) while importing smaller dimension tables for faster performance, giving you the best of both approaches.

How Composite Models Work

In a traditional model, all tables share one storage mode. A composite model removes this constraint. You set storage mode per table:

  • Import: Data loaded into VertiPaq in-memory engine. Fast but only as fresh as the last refresh.
  • DirectQuery: Queries sent to the source in real time. Always current but speed depends on the source.
  • Dual: Stored both ways. Power BI chooses Import when possible, DirectQuery when joined to a DirectQuery table.

When to Use Composite Models

  • Large fact tables: Keep multi-billion-row tables in DirectQuery. Import smaller dimensions for fast slicers.
  • Real-time + historical: DirectQuery for live data, Import for historical aggregates.
  • Multiple data sources: Combine SQL Server (DirectQuery) and Excel (Import) in one model.
  • Aggregation tables: Imported summaries for high-level visuals, falling back to DirectQuery for detail.

Limitations and Considerations

  • Performance trade-offs: Cross-engine relationships can be slower than pure Import.
  • Security: RLS may not enforce on DirectQuery sources — verify source-level security.
  • Calculated columns: Cannot be added to DirectQuery tables. Use Power Query or source views.
  • Deployment: DirectQuery to Power BI datasets or Analysis Services requires Premium/PPU.

How to Create a Composite Model in Power BI

This tutorial walks through setting up a composite model combining Import and DirectQuery in one report. About 5 steps.

Prerequisites

  • Power BI Desktop (latest version)
  • A database supporting DirectQuery (e.g., SQL Server)
  • A smaller dataset (Excel/CSV) to import as a dimension

Step-by-Step

Step 1 — Connect to DirectQuery source. Home > Get Data > SQL Server. Choose DirectQuery. Load the large fact table.

Step 2 — Add an Import table. Get Data again for a smaller table (e.g., Excel). Accept the composite model warning.

Step 3 — Verify storage modes. In Model View, check each table’s Storage Mode property. Fact: DirectQuery. Dimension: Import.

Step 4 — Set a table to Dual (optional). Change a shared dimension to Dual so Power BI optimizes per query.

Step 5 — Create relationships and test. Define relationships in Model View. Build a visual combining live DirectQuery data with imported dimensions.