Relationships (one-to-many, many-to-many) in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to analyze total sales by product category and customer region. You need to build a report that combines sales data with product and customer information.

📊 Data: You have three tables: Sales (with OrderID, ProductID, CustomerID, Quantity, and SalesAmount), Products (with ProductID and Category), and Customers (with CustomerID and Region).

🎯 Deliverable: Create a Power BI report that shows total sales amount by product category and customer region using correct relationships between tables.

Sample Data

Sales
OrderID	ProductID	CustomerID	Quantity	SalesAmount
1001	101	201	2	40
1002	102	202	1	25
1003	103	203	3	75
1004	101	204	1	20
1005	104	201	4	100
1006	102	202	2	50
1007	103	203	1	25
1008	104	204	3	75

Products
ProductID	Category
101	Electronics
102	Clothing
103	Clothing
104	Electronics

Customers
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 tables from your data source.

Expected Result

All three tables appear in Power BI's Fields pane.

2

Step 2: Create a one-to-many relationship between Products and Sales tables.

Drag ProductID from Products (one side) to ProductID in Sales (many side). Set relationship as 'Single' direction.

Expected Result

Relationship created: Products (one) -> Sales (many) on ProductID.

3

Step 3: Create a one-to-many relationship between Customers and Sales tables.

Drag CustomerID from Customers (one side) to CustomerID in Sales (many side). Set relationship as 'Single' direction.

Expected Result

Relationship created: Customers (one) -> Sales (many) on CustomerID.

4

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

Total Sales = SUM(Sales[SalesAmount])

Expected Result

Measure 'Total Sales' is available in the Sales table.

5

Step 5: Build a matrix visual with Product Category as rows and Customer Region as columns.

Add Products[Category] to Rows, Customers[Region] to Columns, and 'Total Sales' measure to Values.

Expected Result

Matrix shows total sales by product category and customer region.

6

Step 6: Verify the data correctness by checking totals.

Check that sum of all matrix values equals SUM(Sales[SalesAmount]) = 410.

Expected Result

Matrix totals sum to 410, matching total sales amount.

Final Result

Total Sales by Product Category and Customer Region

+----------------+--------+--------+--------+--------+--------+
| Category       | North  | South  | East   | West   | Total  |
+----------------+--------+--------+--------+--------+--------+
| Electronics    | 140    | 0      | 0      | 95     | 235    |
| Clothing       | 0      | 75     | 100    | 0      | 175    |
+----------------+--------+--------+--------+--------+--------+
| Total          | 140    | 75     | 100    | 95     | 410    |
+----------------+--------+--------+--------+--------+--------+

✓Electronics category has highest sales in North and West regions.

✓Clothing category sells mostly in South and East regions.

✓Total sales amount across all categories and regions is 410.

Bonus Challenge

Create a many-to-many relationship scenario by adding a Promotions table that links multiple products to multiple promotions, then analyze sales by promotion.

Show Hint

Use a bridge table between Products and Promotions, then create relationships to enable many-to-many filtering.

Practice

1.

What does a one-to-many relationship in Power BI represent?

Choose the best description.

easy

A. Many records in both tables relate to each other.

B. Many records in the first table relate to one record in the second table.

C. No relationship exists between the tables.

D. One record in the first table relates to many records in the second table.

Solution

1. Step 1: Understand one-to-many relationship meaning

   A one-to-many relationship means one record in the first table connects to multiple records in the second table.

2. Step 2: Match description to options

   One record in the first table relates to many records in the second table. correctly describes this connection. Other options describe different or no relationships.

3. Final Answer:

   One record in the first table relates to many records in the second table. -> Option D

4. Quick Check:

   One-to-many = One record to many records [OK]

Hint: One-to-many means single item links to many items [OK]

Common Mistakes:

• Confusing one-to-many with many-to-one
• Thinking many-to-many is the same as one-to-many
• Assuming no relationship means one-to-many

2.

Which of the following is the correct way to define a many-to-many relationship in Power BI?

Choose the correct syntax or description.

easy

A. Use a calculated column to manually link rows between tables.

B. Set relationship cardinality to 'Many to Many' between two tables.

C. Create a relationship with 'One to One' cardinality and enable cross filter.

D. Use DAX function MANYTOMANY() to create the relationship.

Solution

1. Step 1: Recall how relationships are defined in Power BI

   Relationships are set in the model view by choosing cardinality like 'One to Many' or 'Many to Many'.

2. Step 2: Identify the correct method for many-to-many

   Set relationship cardinality to 'Many to Many' between two tables. correctly states setting cardinality to 'Many to Many'. The other options describe incorrect or unsupported methods.

3. Final Answer:

   Set relationship cardinality to 'Many to Many' between two tables. -> Option B

4. Quick Check:

   Many-to-many relationship = Set cardinality to Many to Many [OK]

Hint: Set cardinality property to 'Many to Many' in model view [OK]

Common Mistakes:

• Thinking DAX creates relationships
• Confusing one-to-one with many-to-many
• Trying to link tables with calculated columns

3.

Given two tables: Customers and Orders, with a one-to-many relationship from Customers[CustomerID] to Orders[CustomerID], what will the following DAX measure return?

OrderCount = COUNTROWS(RELATEDTABLE(Orders))

Assuming the measure is used in a table visual showing Customers[CustomerName].

medium

A. The number of orders for each customer.

B. The total number of customers.

C. The total number of orders across all customers.

D. An error because RELATEDTABLE cannot be used here.

Solution

1. Step 1: Understand RELATEDTABLE function in one-to-many context

   RELATEDTABLE returns all rows from the 'many' side (Orders) related to the current row in the 'one' side (Customers).

2. Step 2: COUNTROWS counts those related rows

   So COUNTROWS(RELATEDTABLE(Orders)) counts how many orders belong to each customer.

3. Final Answer:

   The number of orders for each customer. -> Option A

4. Quick Check:

   RELATEDTABLE + COUNTROWS = count related rows per customer [OK]

Hint: RELATEDTABLE counts many-side rows per one-side row [OK]

Common Mistakes:

• Thinking it counts total orders for all customers
• Confusing RELATEDTABLE with RELATED
• Assuming it returns an error

4.

You created a many-to-many relationship between Products and Sales tables but your report shows incorrect totals. What is the most likely cause?

medium

A. The many-to-many relationship causes ambiguous filter context.

B. The relationship is inactive and needs to be activated.

C. You forgot to add a calculated column to link the tables.

D. The data types of the keys do not match.

Solution

1. Step 1: Understand many-to-many relationship effects

   Many-to-many relationships can create ambiguous filter contexts because multiple rows on both sides can match.

2. Step 2: Identify cause of incorrect totals

   This ambiguity often causes totals to be incorrect or duplicated, which matches The many-to-many relationship causes ambiguous filter context.

3. Final Answer:

   The many-to-many relationship causes ambiguous filter context. -> Option A

4. Quick Check:

   Many-to-many can cause ambiguous filters = incorrect totals [OK]

Hint: Many-to-many can confuse filters causing wrong totals [OK]

Common Mistakes:

• Assuming inactive relationship is the cause
• Thinking calculated columns fix relationships
• Ignoring data type mismatches

5.

You have three tables: Students, Courses, and Enrollments. Enrollments links Students and Courses with many-to-many relationships. How should you model these relationships in Power BI to correctly analyze which students took which courses?

hard

A. Create one-to-one relationships between all tables.

B. Create a single many-to-many relationship directly between Students and Courses.

C. Create two one-to-many relationships: Students to Enrollments and Courses to Enrollments.

D. Use calculated columns to merge Students and Courses into Enrollments.

Solution

1. Step 1: Understand the role of the Enrollments table

   Enrollments acts as a bridge table linking Students and Courses with many-to-many data.

2. Step 2: Model relationships correctly

   Best practice is to create two one-to-many relationships: Students (one) to Enrollments (many), and Courses (one) to Enrollments (many). This avoids complex many-to-many direct links.

3. Final Answer:

   Create two one-to-many relationships: Students to Enrollments and Courses to Enrollments. -> Option C

4. Quick Check:

   Bridge table uses two one-to-many relationships [OK]

Hint: Use bridge table with two one-to-many relationships [OK]

Common Mistakes:

• Trying to create many-to-many directly between Students and Courses
• Using one-to-one relationships incorrectly
• Merging tables with calculated columns instead of relationships