Modeling Many-to-Many Relationships in Power BI
Which TWO of the following are best practices for modeling many-to-many relationships in Power BI?
Quick Answer
The correct answer is to use a bridge table and a measure with SUMX. A bridge table normalizes the many-to-many relationship by sitting between the two fact or dimension tables, allowing each side to connect through unique keys, while the SUMX function iterates over that bridge table to correctly aggregate values without double-counting. On the Microsoft Power BI Data Analyst PL-300 exam, this pattern tests your understanding of resolving ambiguity in data modeling—a common trap is relying on bidirectional filtering, which can produce unpredictable results due to cross-filtering ambiguity. Remember that a bridge table acts as an intermediary, and SUMX ensures row-by-row evaluation for accurate totals. For a quick memory tip, think “Bridge + SUMX = clean many-to-many,” and avoid bidirectional filters unless you fully control the filter context.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Use a bridge table to resolve the many-to-many relationship
Both using a bridge table and using a measure that iterates over it with SUMX are best practices for modeling many-to-many relationships in Power BI. A bridge table, when properly configured with many-to-many cardinality or CROSSFILTER, enables correct filtering. A SUMX measure over the bridge table ensures accurate aggregations. Setting cross-filter direction to both (Option B) can cause ambiguous filtering and performance issues. Composite models with DirectQuery (Option C) are not a specific best practice for this scenario. Creating a calculated table (Option E) does not properly resolve the relationship and often leads to data duplication.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Use a bridge table to resolve the many-to-many relationship
Why this is correct
Using a bridge table alone does not configure the many-to-many relationship; you typically need to adjust cross-filter direction or use many-to-many cardinality.
- ✗
Set cross-filter direction to both for all relationships
Why it's wrong here
Can cause ambiguous filter propagation.
- ✗
Use a composite model with DirectQuery for one of the tables
Why it's wrong here
Not directly related to many-to-many.
- ✓
Use a measure that iterates over the bridge table with SUMX
Why this is correct
Using a measure with SUMX that iterates over the bridge table is a common pattern to aggregate correctly and is a best practice.
- ✗
Create a calculated table that combines the two tables
Why it's wrong here
Not necessary and may cause duplication.
Visual reference
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 217-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on PL-300
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. You are modeling a many-to-many relationship between 'Student' and 'Class' tables. Which approach should you use in Power BI to handle this?
easy- A.Merge both tables into one
- B.Use bidirectional cross-filtering
- ✓ C.Add a bridge table with composite keys
- D.Create a single one-to-many relationship
Why C: A many-to-many relationship in Power BI requires a bridge (or junction) table that contains composite keys (e.g., StudentID and ClassID) to resolve the relationship into two one-to-many relationships. This allows Power BI to properly filter and aggregate data across both tables without ambiguity.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.