Relationships (one-to-many, many-to-many) in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding One-to-Many Relationships

In Power BI, you have two tables: Customers and Orders. Each customer can have multiple orders, but each order belongs to only one customer.

What type of relationship should you create between these tables?

A One-to-one between Customers and Orders

B One-to-many from Customers to Orders

C Many-to-many between Customers and Orders

D Many-to-one from Customers to Orders

💡 Hint

Think about how many orders one customer can have.

Since one customer can have many orders, but each order belongs to only one customer, the correct relationship is one-to-many from Customers to Orders.

❓ dax_lod_result

intermediate

DAX Measure with Many-to-Many Relationship

You have two tables: Products and Sales. Products can appear in multiple sales, and sales can include multiple products through a bridge table SalesProducts. This is a many-to-many relationship.

Given this DAX measure, what is the total sales amount for ProductID 101?

SalesAmountForProduct101 = CALCULATE(SUM(Sales[Amount]), FILTER(Products, Products[ProductID] = 101))

A An error due to ambiguous relationship

B The sum of Sales[Amount] for all sales, ignoring ProductID filter

C Zero, because the filter on Products does not propagate through many-to-many

D The sum of Sales[Amount] for all sales containing ProductID 101

💡 Hint

Consider how filter context works with many-to-many via bridge tables.

The FILTER on Products with ProductID 101 filters the bridge table and then Sales, so the measure sums only sales that include ProductID 101.

❓ visualization

advanced

Visualizing Many-to-Many Relationships

You want to create a Power BI report showing total sales by Customer and Product Category. Customers can buy products from multiple categories, and categories have many products. The data model uses a many-to-many relationship between Customers and Product Categories through Sales.

Which visualization best shows total sales by Customer and Product Category with clear understanding?

A Matrix visual with Customers as rows and Product Categories as columns showing total sales

B Stacked bar chart with Customers on X-axis and total sales stacked by Product Category

C Pie chart showing total sales by Product Category only

D Line chart showing total sales over time without categories

💡 Hint

Think about how to show two categorical dimensions and their sales values clearly.

A matrix visual allows cross-tabulation of Customers and Product Categories, showing total sales clearly for each combination, which is ideal for many-to-many relationships.

🔧 Formula Fix

advanced

Debugging Relationship Filter Propagation

You created a many-to-many relationship between Students and Courses through an enrollment bridge table. You wrote this DAX measure to count courses per student:

CourseCount = COUNTROWS(RELATEDTABLE(Courses))

But the measure always returns 0. What is the most likely reason?

A RELATEDTABLE does not work across many-to-many relationships without proper filter direction

B COUNTROWS cannot be used on the Courses table

C The relationship should be one-to-many, not many-to-many

D The measure syntax is incorrect and causes an error

💡 Hint

Check how filter direction affects RELATEDTABLE in many-to-many setups.

RELATEDTABLE requires a filter to flow from the current table to the related table. In many-to-many relationships, filter direction may not propagate as expected, causing RELATEDTABLE to return empty.

🎯 Scenario

expert

Designing a Data Model with Mixed Relationships

Your company tracks Employees, Projects, and Skills. Each employee can work on many projects (one-to-many), and employees can have multiple skills while skills can belong to many employees (many-to-many).

Which data model design correctly represents these relationships in Power BI?

A Create many-to-many relationships between Employees and Projects, and Employees and Skills without bridge tables

B Create one-to-one relationships between Employees and Projects, and Employees and Skills

C Create a one-to-many relationship from Employees to Projects, and a many-to-many relationship between Employees and Skills using a bridge table

D Create one-to-many relationships from Skills to Employees and Projects

💡 Hint

Consider the natural cardinality of each relationship and how bridge tables help many-to-many.

Employees to Projects is one-to-many, so a direct relationship works. Employees to Skills is many-to-many, so a bridge table is needed to model it correctly.

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