Composite models in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report that combines detailed sales data from a local Excel file with summarized customer data from a cloud-based SQL database. The goal is to analyze sales performance by customer segments without importing all customer data locally.

📊 Data: You have two data sources: 1) An Excel file with detailed sales transactions including Date, Product, CustomerID, and SalesAmount. 2) A SQL database with customer information including CustomerID, Segment, and Region.

🎯 Deliverable: Create a Power BI report using a composite model that combines the imported sales data with the direct query customer data. Build a visual showing total sales by customer segment.

Sample Data

Date	Product	CustomerID	SalesAmount
2024-01-05	Widget A	C001	150
2024-01-07	Widget B	C002	200
2024-01-10	Widget A	C003	120
2024-01-15	Widget C	C001	180
2024-01-20	Widget B	C004	220
2024-01-22	Widget C	C002	160
2024-01-25	Widget A	C005	130
2024-01-28	Widget B	C003	210

1

Step 1: Import the sales data Excel file into Power BI as an imported table named 'Sales'.

Use Power BI Desktop: Home > Get Data > Excel > Select file > Load data as Import mode.

Expected Result

Sales table with 8 rows loaded into Power BI.

2

Step 2: Connect to the SQL database containing customer data using DirectQuery mode.

Home > Get Data > SQL Server > Enter server and database > Choose DirectQuery mode > Load 'Customer' table.

Expected Result

Customer table with columns CustomerID, Segment, and Region available via DirectQuery.

3

Step 3: Create a relationship between 'Sales'[CustomerID] and 'Customer'[CustomerID] in the model view.

Model view > Drag 'CustomerID' from Sales to Customer > Set relationship as Many-to-One, Single direction.

Expected Result

Active relationship established between Sales and Customer tables.

4

Step 4: Create a measure to calculate total sales amount.

Total Sales = SUM(Sales[SalesAmount])

Expected Result

Measure 'Total Sales' created showing sum of SalesAmount.

5

Step 5: Build a bar chart visual with 'Customer'[Segment] on the axis and 'Total Sales' as values.

Visualizations pane > Bar chart > Axis = Customer[Segment], Values = Total Sales measure.

Expected Result

Bar chart showing total sales grouped by customer segment.

Final Result

Sales by Customer Segment

Segment A  | ██████████  $660
Segment B  | ███████     $370
Segment C  | ████        $280

(Bar lengths represent sales amounts)

✓Segment A customers generated the highest total sales of $660.

✓Segment B and C have lower sales, indicating potential growth opportunities.

✓Using composite models allowed combining detailed sales data with live customer info without full data import.

Bonus Challenge

Add a slicer to filter the sales report by Region from the Customer table using DirectQuery.

Show Hint

Use the 'Region' column from the Customer table in a slicer visual. The composite model will apply the filter dynamically.

Practice

1.

What is the main benefit of using composite models in Power BI?

easy

A. They only work with Import mode data.

B. They allow combining data from multiple sources in one report.

C. They automatically clean data without user input.

D. They replace the need for relationships between tables.

Solution

1. Step 1: Understand composite models purpose

   Composite models let you combine data from different sources in one Power BI report.

2. Step 2: Compare options

   Only 'They allow combining data from multiple sources in one report.' correctly states this benefit; others are incorrect or unrelated.

3. Final Answer:

   They allow combining data from multiple sources in one report. -> Option B

4. Quick Check:

   Composite models = combine multiple sources [OK]

Hint: Composite models = mix data sources in one report [OK]

Common Mistakes:

• Thinking composite models only work with Import mode
• Believing composite models clean data automatically
• Assuming relationships are not needed in composite models

2.

Which of the following is the correct way to enable composite models in Power BI?

Options > Preview Features > Enable Composite Models

easy

A. Enable Composite Models under Options > Preview Features

B. Enable Composite Models under File > Export

C. Enable Composite Models under Home > Transform Data

D. Enable Composite Models under View > Themes

Solution

1. Step 1: Locate composite models setting

   Composite models are enabled via Options > Preview Features in Power BI Desktop.

2. Step 2: Verify other options

   Other options do not contain this setting, so only Enable Composite Models under Options > Preview Features is correct.

3. Final Answer:

   Enable Composite Models under Options > Preview Features -> Option A

4. Quick Check:

   Composite models setting = Options > Preview Features [OK]

Hint: Check Preview Features in Options for composite models [OK]

Common Mistakes:

• Looking for composite models in Export or Themes menus
• Confusing data transformation with enabling composite models
• Assuming composite models are enabled by default

3.

Given a composite model with two tables: Sales (Import mode) and Customers (DirectQuery mode), what happens when you create a relationship between them?

medium

A. The relationship only works if both tables are in Import mode.

B. Power BI throws an error because relationships cannot connect Import and DirectQuery tables.

C. The relationship works but disables DirectQuery mode for the Customers table.

D. Power BI allows cross-source relationships, enabling combined filtering and analysis.

Solution

1. Step 1: Understand composite model relationships

   Composite models allow relationships between Import and DirectQuery tables.

2. Step 2: Analyze options

   Only 'Power BI allows cross-source relationships, enabling combined filtering and analysis.' correctly states what happens; others are false.

3. Final Answer:

   Power BI allows cross-source relationships, enabling combined filtering and analysis. -> Option D

4. Quick Check:

   Composite models = cross-source relationships allowed [OK]

Hint: Composite models support relationships across Import and DirectQuery [OK]

Common Mistakes:

• Believing relationships cannot connect different storage modes
• Thinking DirectQuery tables lose their mode when related
• Assuming relationships only work within same storage mode

4.

You created a composite model with Import and DirectQuery tables but get an error when trying to build a relationship. What is a likely cause?

medium

A. The composite models feature is not enabled in Preview Features.

B. You used the wrong DAX syntax for the relationship.

C. Power BI does not support relationships between tables.

D. The data sources are from the same database.

Solution

1. Step 1: Check composite models feature status

   If composite models are not enabled in Preview Features, relationships between Import and DirectQuery tables cause errors.

2. Step 2: Evaluate other options

   DAX syntax is not used to create relationships; Power BI supports relationships; same database does not cause errors.

3. Final Answer:

   The composite models feature is not enabled in Preview Features. -> Option A

4. Quick Check:

   Enable composite models to allow cross-source relationships [OK]

Hint: Enable composite models in Preview Features to avoid relationship errors [OK]

Common Mistakes:

• Trying to fix relationships with DAX syntax
• Assuming relationships are unsupported in Power BI
• Thinking same database causes relationship errors

5.

You have a composite model with three tables: Orders (Import), Products (DirectQuery), and Customers (Import). You want to create a report showing total sales by product category and customer region. Which approach is best?

hard

A. Use only the Orders table and ignore others to simplify the report.

B. Import all tables to avoid composite models and relationships.

C. Create relationships between all tables, use DirectQuery for Products, and build visuals combining all data.

D. Create separate reports for each table and combine them outside Power BI.

Solution

1. Step 1: Understand composite model usage

   Composite models allow mixing Import and DirectQuery tables with relationships for combined analysis.

2. Step 2: Apply to scenario

   Creating relationships between all tables and using DirectQuery for Products enables combined sales by product category and customer region.

3. Step 3: Evaluate other options

   Importing all tables may not be possible; ignoring tables loses insights; separate reports reduce integration.

4. Final Answer:

   Create relationships between all tables, use DirectQuery for Products, and build visuals combining all data. -> Option C

5. Quick Check:

   Composite models + relationships = combined report [OK]

Hint: Use relationships to combine Import and DirectQuery tables in one report [OK]

Common Mistakes:

• Trying to avoid composite models by importing all data
• Ignoring important tables for simplicity
• Splitting data into separate reports unnecessarily