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.