Why data modeling connects related tables in Power BI - Business Case Study

Scenario Mode

👤 Your Role: You are a business analyst working with sales data.

📋 Request: Your manager wants you to create a report that shows total sales by product category and customer region. The data is in separate tables, and you need to connect them properly to get accurate results.

📊 Data: You have three tables: Sales (with sales amounts and product IDs), Products (with product IDs and categories), and Customers (with customer IDs and regions).

🎯 Deliverable: Create a data model in Power BI that connects these tables correctly, then build a report showing total sales by product category and customer region.

Sample Data

SalesID	ProductID	CustomerID	SalesAmount
1	101	201	100
2	102	202	150
3	101	203	200
4	103	201	50
5	102	204	120
6	104	202	80

ProductID	Category
101	Electronics
102	Furniture
103	Electronics
104	Office Supplies

CustomerID	Region
201	North
202	South
203	East
204	West

1

Step 1: Load the three tables (Sales, Products, Customers) into Power BI.

Use 'Get Data' to import all three tables from your data source.

Expected Result

All three tables appear in Power BI's data view.

2

Step 2: Create relationships between the tables to connect related data.

In the Model view, drag ProductID from Sales table to ProductID in Products table. Then drag CustomerID from Sales table to CustomerID in Customers table.

Expected Result

Two one-to-many relationships are created: Products to Sales, and Customers to Sales.

3

Step 3: Verify relationships are active and set to single direction filtering from Products and Customers to Sales.

Check relationship properties to ensure 'Cross filter direction' is 'Single' and 'Active' is checked.

Expected Result

Relationships filter Sales data correctly based on Products and Customers.

4

Step 4: Create a report with a matrix visual to show total sales by product category and customer region.

Add a Matrix visual. Place 'Category' from Products table in Rows, 'Region' from Customers table in Columns, and sum of 'SalesAmount' from Sales table in Values.

Expected Result

Matrix shows total sales amounts broken down by product category and customer region.

5

Step 5: Check that the totals are correct by manually summing sales for each category and region.

Manually sum sales amounts for Electronics in North region: SalesID 1 (100) + SalesID 4 (50) = 150.

Expected Result

Matrix shows 150 for Electronics in North region, matching manual calculation.

Final Result

---------------------------------------------
|           Sales by Category and Region     |
---------------------------------------------
| Category       | North | South | East | West|
|------------------------------------------------
| Electronics    |  150  |   0   |  200 |  0  |
| Furniture      |   0   |  150  |   0  | 120 |
| Office Supplies|   0   |   80  |   0  |  0  |
---------------------------------------------

✓Electronics products have the highest sales in the North and East regions.

✓Furniture sales are strong in the South and West regions.

✓Office Supplies sales are only in the South region.

✓Connecting tables with relationships allows accurate aggregation across categories and regions.

Bonus Challenge

Add a Date table and connect it to the Sales table to analyze sales trends over time.

Show Hint

Create a Date table with continuous dates, then create a relationship between Date and Sales on the sales date column.

Practice

1. Why do we connect related tables in Power BI data modeling?

easy

A. To make tables look colorful in the model view

B. To combine related data for accurate analysis and reporting

C. To delete duplicate rows automatically

D. To speed up Power BI software installation

Solution

1. Step 1: Understand the purpose of connecting tables

   Connecting tables links related data so you can analyze combined information easily.

2. Step 2: Recognize the benefit in reports

   Proper connections ensure reports show accurate results by relating data correctly.

3. Final Answer:

   To combine related data for accurate analysis and reporting -> Option B

4. Quick Check:

   Connecting tables = accurate combined data [OK]

Hint: Think: Why join data? To analyze together [OK]

Common Mistakes:

• Confusing visual formatting with data relationships
• Assuming connections delete duplicates
• Believing connections affect software speed

2. Which of the following is the correct way to create a relationship between two tables in Power BI?

easy

A. Use the Format pane to link tables by color

B. Write a DAX formula to join tables in the Report view

C. Drag a key column from one table to the matching column in another table in Model view

D. Copy and paste data from one table into another

Solution

1. Step 1: Identify how relationships are created in Power BI

   Relationships are made by dragging matching key columns between tables in Model view.

2. Step 2: Eliminate incorrect methods

   DAX formulas do calculations but do not create relationships; formatting and copy-paste do not link tables.

3. Final Answer:

   Drag a key column from one table to the matching column in another table in Model view -> Option C

4. Quick Check:

   Drag key columns = create relationship [OK]

Hint: Drag keys in Model view to connect tables [OK]

Common Mistakes:

• Trying to create relationships using DAX formulas
• Confusing formatting with data linking
• Copy-pasting data instead of linking

3. Given two tables: Sales with columns OrderID, ProductID, and Amount, and Products with columns ProductID and ProductName. If you create a relationship on ProductID between these tables, what will happen when you create a report showing ProductName and total Amount?

medium

A. The report will show total sales amount grouped by product name

B. The report will show blank values for product names

C. The report will show total amount without grouping

D. The report will cause an error and not display

Solution

1. Step 1: Understand the relationship effect

   Connecting on ProductID links sales to product details, enabling grouping by product name.

2. Step 2: Predict report output

   Using ProductName and total Amount will group sales amounts by each product correctly.

3. Final Answer:

   The report will show total sales amount grouped by product name -> Option A

4. Quick Check:

   Relationship on ProductID = grouped sales by product [OK]

Hint: Relationship keys enable grouping related data [OK]

Common Mistakes:

• Expecting blank product names without relationship
• Thinking totals show without grouping
• Assuming report errors without relationship

4. You created a relationship between two tables on the CustomerID column, but your report shows incorrect totals. What is the most likely issue?

medium

A. The CustomerID columns have mismatched data types or missing values

B. You forgot to format the report colors

C. The tables are too large to connect

D. Power BI does not support relationships on ID columns

Solution

1. Step 1: Check relationship keys

   For relationships to work, key columns must have matching data types and no missing values.

2. Step 2: Identify cause of incorrect totals

   If data types differ or values are missing, relationships break, causing wrong report results.

3. Final Answer:

   The CustomerID columns have mismatched data types or missing values -> Option A

4. Quick Check:

   Matching keys needed for correct totals [OK]

Hint: Check key columns data type and completeness [OK]

Common Mistakes:

• Blaming report colors for data errors
• Assuming table size prevents relationships
• Thinking Power BI disallows ID relationships

5. You have three tables: Orders, Customers, and Regions. Orders links to Customers by CustomerID, and Customers links to Regions by RegionID. You want to create a report showing total sales by region. What is the best data modeling approach?

hard

A. Create a relationship only between Orders and Regions directly

B. Join all tables into one big table before importing to Power BI

C. Use DAX to manually merge tables in the report

D. Create relationships from Orders to Customers on CustomerID, and Customers to Regions on RegionID, then use RegionName in the report

Solution

1. Step 1: Understand table connections

   Orders relate to Customers by CustomerID; Customers relate to Regions by RegionID, forming a chain.

2. Step 2: Model relationships properly

   Create relationships along this chain so data flows correctly from Orders to Regions.

3. Step 3: Use RegionName in report

   With relationships, you can group total sales by RegionName accurately.

4. Final Answer:

   Create relationships from Orders to Customers on CustomerID, and Customers to Regions on RegionID, then use RegionName in the report -> Option D

5. Quick Check:

   Chain relationships enable accurate regional sales [OK]

Hint: Connect tables stepwise using keys for multi-table reports [OK]

Common Mistakes:

• Trying to flatten tables before import unnecessarily
• Skipping intermediate relationships
• Using DAX merges instead of model relationships