Microsoft Power BI Quiz: Relationships
10 questions · exam conditions
0:00
RelationshipsQuestion 1 of 10

A Power BI model contains a Customers table loaded from a CRM system and a Sales table loaded from an ERP system. Customers contains multiple rows for some CustomerID values because each synchronization appends another copy of the customer's current record. Sales can contain many rows per CustomerID. Reports must show sales by each customer's current attributes.

You need to create a relationship that supports accurate filtering and follows star-schema design. What should you do?

Remove duplicate CustomerID rows while retaining the current record, then create a one-to-many relationship with single-direction filtering from Customers to Sales.
Keep every customer record, then create a many-to-many relationship with bidirectional filtering between Customers and Sales.
Remove duplicate sales transactions by CustomerID, then create a one-to-one relationship with bidirectional filtering between the tables.
Keep every customer record, then create a many-to-one relationship with single-direction filtering from Sales to Customers.
← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Relationships

Practice Relationships in Microsoft Power BI with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Relationships, giving you a quick way to practice the rules, question types, and explanations that matter most for Microsoft Power BI.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A Power BI model contains a Customers table loaded from a CRM system and a Sales table loaded from an ERP system. Customers contains multiple rows for some CustomerID values because each synchronization appends another copy of the customer's current record. Sales can contain many rows per CustomerID. Reports must show sales by each customer's current attributes.

You need to create a relationship that supports accurate filtering and follows star-schema design. What should you do?

  1. Remove duplicate CustomerID rows while retaining the current record, then create a one-to-many relationship with single-direction filtering from Customers to Sales. (correct answer)
  2. Keep every customer record, then create a many-to-many relationship with bidirectional filtering between Customers and Sales.
  3. Remove duplicate sales transactions by CustomerID, then create a one-to-one relationship with bidirectional filtering between the tables.
  4. Keep every customer record, then create a many-to-one relationship with single-direction filtering from Sales to Customers.
Explanation: When designing relationships in Power BI, star-schema principles should guide your thinking: a clean dimension table (one row per key) filters a fact table through a one-to-many relationship. Whenever you see duplicate keys in a dimension table caused by synchronization artifacts, your first job is to fix the data before building the relationship. Answer A is correct because it addresses the root problem. By deduplicating the Customers table in Power Query — keeping only each customer's most recent record — you create a proper dimension with unique CustomerID values. From there, a one-to-many relationship with single-direction filtering from Customers to Sales correctly propagates filters downstream, ensuring reports reflect each customer's current attributes without ambiguity. Answer B is wrong on two counts. Keeping every duplicate record means CustomerID is not unique in Customers, which forces a many-to-many relationship. Many-to-many relationships in Power BI use a hidden bridge table internally and can produce unexpected, inflated results — they should be a last resort, not a design choice made simply to avoid cleaning data. Bidirectional filtering compounds this by creating potential for circular filter propagation and unpredictable aggregations. Answer C is incorrect because removing duplicate Sales rows violates the fact table's integrity — you'd lose real transactions. A one-to-one relationship also doesn't make sense here, since one customer can have many sales. Answer D leaves the duplicates in place, so the relationship still can't be properly defined with a unique key on the Customers side; Power BI would require a many-to-many configuration, not many-to-one. Study tip: On Power BI exams, always resolve data quality issues (duplicates, nulls) in Power Query before defining relationships — clean dimensions are the foundation of reliable star schemas.

Question 2

Students can enroll in many courses, and each course can have many students. An Enrollment table contains one row per StudentID and CourseID combination. A report must count enrolled students by course and filter enrollment measures by student or course attributes. It is not required for course selections to filter columns displayed directly from Students.

Which relationship design best meets the requirements while minimizing ambiguous filter propagation?

  1. Relate Students and Courses directly by using many-to-many cardinality and set the relationship's cross-filter direction to Both.
  2. Relate Students and Courses directly by using one-to-many cardinality and filter from Students toward Courses.
  3. Relate each dimension one-to-many to Enrollment and use single-direction filtering from Students and Courses to Enrollment. (correct answer)
  4. Relate each dimension one-to-many to Enrollment and use bidirectional filtering on both relationships.
Explanation: When designing relationships in Power BI for a classic many-to-many scenario (students enrolling in courses), your goal is to enable flexible filtering without creating ambiguous or unintended filter paths. The Enrollment table is your bridge entity — it naturally sits between Students and Courses, and your relationship design should respect that structure. The cleanest solution is C: relate Students → Enrollment and Courses → Enrollment, both as one-to-many with single-direction filtering pointing into Enrollment. This means filters from Students or Courses flow into Enrollment (letting you count enrolled students per course, filter by student attributes, etc.), but filters do not propagate between Students and Courses through Enrollment. Since the requirements explicitly state that course selections don't need to filter student columns, this unidirectional setup is perfectly sufficient — and it avoids ambiguity. A is tempting but wrong. A direct many-to-many relationship with bidirectional cross-filtering between Students and Courses bypasses the Enrollment bridge entirely, losing enrollment-level granularity and creating unpredictable filter propagation across both tables. B fails on cardinality. A one-to-many relationship directly between Students and Courses misrepresents the data structure — one student can enroll in many courses and one course can have many students, so no valid one-to-many exists between them without the bridge. D uses the correct bridge table structure but adds bidirectional filtering on both relationships. This causes filters to "leak" from Courses through Enrollment into Students (and vice versa), creating the exact ambiguity the requirements say you don't need. Study tip: When you see a bridge/junction table in Power BI, default to single-direction filtering toward the bridge — only enable bidirectional filtering when cross-table filtering is explicitly required.

Question 3

A model has Customer and Product dimensions connected to a Sales fact table by one-to-many, single-direction relationships. A report contains a Product slicer and a Customer slicer. The Customer slicer must display only customers who purchased products currently selected in the Product slicer. No other relationships exist in the model.

What is the minimum relationship change that will meet the requirement?

  1. Set only the Product-to-Sales relationship to Both while leaving the Customer-to-Sales relationship single-directional.
  2. Set only the Customer-to-Sales relationship to Both while leaving the Product-to-Sales relationship single-directional. (correct answer)
  3. Set both dimension-to-Sales relationships to Both so each slicer can filter through the fact table.
  4. Reverse both relationships to filter only from Sales toward the Customer and Product dimensions.
Explanation: When working with cross-filter direction in Power BI, ask yourself: which table needs to "receive" filtered context from another table it isn't directly filtering? In a star schema, filters naturally flow from dimension tables into fact tables (one-to-many, single direction). To push filtering the other way — from fact to dimension — you need bidirectional cross-filtering. Here, the requirement is that selecting a Product filters the Customer slicer. The filter path needed is: Product → Sales → Customer. Product already filters Sales naturally (single direction). The missing link is Sales → Customer, meaning the Customer-to-Sales relationship must be set to Both so that Sales can propagate the filtered context up into the Customer dimension. That's exactly what B does — and it's the minimum change because only one relationship needs to change. A is backwards. Setting Product-to-Sales to Both allows Sales to filter Products, but that's the wrong direction entirely — Products are filtering Sales, not the other way around. This change does nothing to expose filtered customers. C sets both relationships to bidirectional, which technically works but violates the "minimum change" requirement. It also introduces unnecessary ambiguity and potential performance issues with two bidirectional relationships in the same schema. D is a misconception about how relationship direction works. Reversing relationships would break the standard fact-table filtering pattern entirely and wouldn't produce the desired cross-filtering behavior. A useful rule of thumb: bidirectional filtering is needed on the side of the dimension you want filtered indirectly. Change only that relationship to meet the minimum requirement.

Question 4

A Targets table contains exactly one row for each combination of StoreID and FiscalYear. A Sales table contains many transactions for each of those combinations. StoreID values repeat across years in both tables, and FiscalYear values repeat across stores. Power BI relationships can use only one column from each table.

You need a direct relationship at the target grain without incorrectly combining different stores or years. What should you do?

  1. Relate the tables on StoreID by using many-to-many cardinality and allow FiscalYear filters to resolve the remaining matches.
  2. Create separate active relationships on StoreID and FiscalYear and set both relationships to bidirectional filtering.
  3. Create a stable StoreID-FiscalYear key in both tables and define a one-to-many, single-direction relationship from Targets to Sales. (correct answer)
  4. Relate the tables on FiscalYear by using one-to-many cardinality and apply StoreID only as a report-level filter.
Explanation: Whenever you see a Power BI question involving a compound grain — meaning uniqueness requires two columns together — your first instinct should be to ask: "Can a single column relationship correctly identify each row?" If the answer is no, you need a composite key. In this scenario, StoreID repeats across years and FiscalYear repeats across stores. Neither column alone uniquely identifies a row in the Targets table. The solution in C is correct because concatenating (or otherwise combining) StoreID and FiscalYear into a single surrogate key — for example, "Store1-2024" — creates a column that is unique in Targets and maps correctly to many rows in Sales. You can then define a clean one-to-many relationship from Targets (one unique key per row) to Sales (many transactions sharing that same key), with single-direction filtering so context flows naturally from Targets into Sales. A is a trap: a many-to-many relationship on StoreID alone would allow Store 1 in 2023 to bleed into Store 1 in 2024, producing incorrect aggregations. FiscalYear filters on a report page cannot reliably fix a structurally wrong relationship. B is invalid because Power BI only allows one active relationship between any two tables. You cannot activate two relationships simultaneously, and even if you could, the engine would still lack a single unambiguous join path. D makes the same fundamental mistake as A — relating on a single non-unique column — and pushing StoreID to a report-level filter is not a data-model fix; it's a fragile workaround that breaks in any visual where StoreID isn't explicitly filtered. As a study rule: when two tables share a compound grain, always build a composite key before defining a relationship — don't rely on cardinality settings or report filters to compensate for a structurally incomplete join.

Question 5

An Employees table contains one row per EmployeeID. An EmployeeDetails table also contains one row per EmployeeID and stores optional attributes that are maintained separately. EmployeeID is unique in both tables. Users must be able to start with fields from either table and have filters propagate to the other table.

Which relationship configuration is appropriate?

  1. Use a one-to-one relationship with single-direction filtering from Employees to EmployeeDetails, because the dimension owner table should always control filter flow.
  2. Use a one-to-many relationship from Employees and apply single-direction filtering toward EmployeeDetails.
  3. Use a many-to-many relationship and apply bidirectional filtering because both tables can initiate filters.
  4. Use a one-to-one relationship with bidirectional cross-filtering, because EmployeeID is unique in both tables and filters must propagate in either direction. (correct answer)
Explanation: When you see a question about table relationships in Power BI, ask yourself two things: What is the cardinality? and In which direction must filters flow? Both determine which relationship type to configure. Here, EmployeeID is unique in both the Employees and EmployeeDetails tables — one row per ID in each. That's the textbook definition of a one-to-one relationship. Because users must be able to start filtering from either table and have those filters propagate to the other, you need bidirectional cross-filtering. Single-direction filtering would block filters originating from whichever side isn't the "source." Putting these together confirms D as correct: a one-to-one relationship with bidirectional filtering is the precise fit. A is wrong because it uses one-to-one correctly but restricts filtering to one direction. The scenario explicitly requires filters to propagate from either table, so locking filter flow to Employees → EmployeeDetails breaks the requirement. B applies a one-to-many cardinality, which misrepresents the data. Since EmployeeID is unique in both tables, there is no "many" side — modeling it as one-to-many would be factually incorrect and could introduce ambiguity. C reaches for many-to-many unnecessarily. Many-to-many is appropriate when neither side has unique keys. Using it here adds complexity and potential performance overhead with no benefit when a simpler one-to-one relationship already satisfies the requirement. Study tip: On Power BI exam questions, always match cardinality to actual data uniqueness first, then decide filter direction based on the stated business requirement. Don't let bidirectionality push you toward many-to-many when uniqueness is confirmed on both sides.

Question 6

A dynamic row-level security model contains Users, UserTerritory, Territory, and Sales. Users has one row per user, UserTerritory has multiple territory assignments per user, Territory has one row per territory, and Sales has many rows per territory. Security is defined on Users. The filter must propagate from Users through UserTerritory to Territory and then to Sales.

Which relationship configuration enables the security filter to reach Sales while limiting bidirectional behavior to the required bridge path?

  1. Use single-direction relationships throughout and enable bidirectional filtering only between Territory and Sales.
  2. Use single-direction filtering from Users to UserTerritory; set the Territory-to-UserTerritory relationship to Both and enable 'Apply security filter in both directions'; use single-direction filtering from Territory to Sales. (correct answer)
  3. Set every relationship to Both and disable the 'Apply security filter in both directions' option on the Territory-to-UserTerritory bridge relationship.
  4. Relate Users directly to Sales as many-to-many and leave both bridge relationships inactive for security evaluation.
Explanation: When you encounter row-level security (RLS) questions involving bridge tables in Power BI, focus on two things: how filter context propagates across relationships, and where bidirectional filtering is genuinely required versus unnecessarily risky. In this model, the filter originates on Users and must travel through UserTerritory (a many-to-many bridge) to reach Territory, and then flow naturally downstream to Sales. The challenge is that Users-to-UserTerritory is a one-to-many relationship where the filter needs to cross in the non-default direction. Answer B solves this correctly: single-direction filtering from Users into UserTerritory keeps the upstream path clean, then setting the Territory-to-UserTerritory relationship to Both with "Apply security filter in both directions" enabled allows the RLS context to traverse the bridge back up to Territory. From Territory, a standard single-direction relationship carries the filter down into Sales. This is precise, surgical bidirectional filtering — only where the bridge demands it. Answer A fails because enabling bidirectional filtering between Territory and Sales is unnecessary and does nothing to solve the actual bottleneck, which is crossing the UserTerritory bridge from Users to Territory. The filter never reaches Sales because the bridge remains uncrossable. Answer C is dangerous — setting every relationship to Both without the security filter option creates ambiguous filter paths and risks circular dependency errors or unintended data exposure. Answer D eliminates the bridge tables entirely, which defeats the purpose of the many-to-many architecture and would likely cause incorrect or unfiltered results across the model. The key study tip: in bridge-table RLS scenarios, enable bidirectional filtering only on the bridge relationship and always pair it with "Apply security filter in both directions" — nowhere else.

Question 7

Sales contains both CustomerKey and TransactionTerritoryKey. Customers contains CurrentTerritoryKey. Reports must use the territory recorded on each transaction by default. A limited set of measures will instead analyze customers by their current territory and can explicitly override default relationships in DAX. Customer-to-Sales is an active one-to-many relationship.

How should you configure the two relationships from Territory to support the required default behavior without creating competing active filter paths?

  1. Make Territory-to-Sales active and Territory-to-Customers inactive, using single-direction filtering from Territory on both relationships. (correct answer)
  2. Make Territory-to-Customers active and Territory-to-Sales inactive, using single-direction filtering from Territory on both relationships.
  3. Make both Territory relationships active and use bidirectional filtering so Power BI can select the appropriate territory path.
  4. Make both Territory relationships inactive and apply bidirectional filtering only within visuals that display territory information.
Explanation: When your data model has multiple paths from one table to another, Power BI enforces a strict rule: only one active relationship can exist between any two tables at a time, and no two active relationships can create competing filter paths to the same destination. Here, Territory connects to both Sales (via TransactionTerritoryKey) and Customers (via CurrentTerritoryKey), so you must carefully choose which relationship is active. Since reports must default to filtering Sales by the transaction's territory, the Territory-to-Sales relationship should be active. This ensures that every visual using Territory automatically filters transactions correctly without any DAX override. The Territory-to-Customers relationship should be inactive, because only a limited set of measures needs it — those measures can explicitly activate it using USERELATIONSHIP() in DAX. Single-direction filtering from Territory into each table is sufficient and keeps filter flow predictable. This makes A the correct configuration. B has it backwards — making Territory-to-Customers active means territory filters would default to customer current territory, breaking the requirement that transaction territory is the default. C is incorrect because you cannot have two active relationships from Territory creating parallel paths; Power BI would flag an ambiguous relationship error. Bidirectional filtering doesn't resolve that conflict — it often amplifies ambiguity. D renders both relationships inactive, meaning no territory filtering works by default at all, which violates the core requirement. As a study tip, remember this pattern: active = default behavior, inactive = opt-in via DAX. When a question describes a "default" behavior and an "override" behavior, map them directly to active and inactive relationships respectively.

Question 8

A semantic model contains Products, Sales, and Returns. ProductKey is unique in Products but is repeated in both fact tables. A product slicer must filter sales and returns. Filters originating from either fact table must not change the products displayed in the slicer or filter the other fact table.

Which relationship configuration should you use?

  1. Create one-to-many relationships from Products to both fact tables and use single-direction filtering from Products. (correct answer)
  2. Create one-to-many relationships from Products to both fact tables and use bidirectional filtering on both relationships.
  3. Create many-to-many relationships from Products to both fact tables and use single-direction filtering toward Products.
  4. Create a direct many-to-many relationship between Sales and Returns and use bidirectional filtering between them.
Explanation: When designing relationships in Power BI with a shared dimension and two fact tables, your goal is to control how filters flow — specifically, which tables can push filters onto others. This is a classic star schema with a shared dimension scenario. The requirement here has two parts: the Products slicer must filter both fact tables, and filters from either fact table must not propagate back to Products or cross over to the other fact table. Answer A achieves exactly this. One-to-many relationships from Products to Sales and from Products to Returns, with single-direction filtering pointing from Products toward the fact tables, means filters flow one way: Products → Sales and Products → Returns. The slicer sits on the "one" side and drives both fact tables cleanly. No filter can travel backward from a fact table to Products or sideways to the other fact table. Answer B is tempting but dangerous. Enabling bidirectional filtering on both relationships allows filters to flow from the fact tables back into Products and potentially cross-filter each other through the shared dimension — violating the isolation requirement and risking incorrect slicer behavior. Answer C introduces many-to-many relationships unnecessarily. Since ProductKey is unique in Products, a standard one-to-many relationship is the correct and more predictable choice. Many-to-many adds complexity and ambiguity without benefit here. Answer D completely ignores Products as a dimension and tries to create a direct relationship between two fact tables — a fundamentally flawed data modeling approach that would not enable the product slicer behavior described. A good study rule: in star schemas, single-direction filtering from dimension to fact is the default safe choice. Only add bidirectional filtering when you have a specific, justified need — and know its side effects.

Question 9

A Sales table contains OrderDate and ShipDate. A Date table has one row per calendar date. Most existing measures must respond to Date slicers by OrderDate, but a new measure must calculate sales by ShipDate without changing the behavior of the existing measures.

How should you manage the relationships?

  1. Keep the OrderDate relationship active, create an inactive ShipDate relationship, and activate ShipDate within the new measure by using USERELATIONSHIP. (correct answer)
  2. Make both date relationships active, set both to bidirectional filtering, and select ShipDate in the new visual's filter pane.
  3. Make the ShipDate relationship active, leave OrderDate inactive, and activate OrderDate within only the new measure by using USERELATIONSHIP.
  4. Keep only the OrderDate relationship and change its cross-filter direction to Both whenever the new ShipDate measure is evaluated.
Explanation: When a fact table has two date columns (like OrderDate and ShipDate) that both relate to the same Date table, you can only have one active relationship at a time in Power BI. The active relationship is what responds automatically to slicers and filters — so your choice of which relationship is active determines the default behavior across all measures. Since the requirement states that most existing measures must respond to Date slicers by OrderDate, the OrderDate relationship must remain active. That way, all your existing measures continue working without modification. For the new ShipDate measure, you use USERELATIONSHIP inside a CALCULATE function to temporarily activate the inactive ShipDate relationship during that specific calculation only — without affecting anything else. This is exactly what answer A describes, making it the correct approach. Answer B fails because Power BI does not allow two active relationships between the same two tables. Bidirectional filtering is a separate concept (controlling filter propagation direction) and does not resolve the constraint on active relationships — this answer conflates two different settings. Answer C gets things backwards. Making ShipDate active would break all existing measures that expect OrderDate filtering, forcing you to wrap every existing measure in USERELATIONSHIP just to restore their original behavior — a maintenance nightmare. Answer D is incorrect because cross-filter direction controls which direction filters flow between tables, not which column is used as the join key. Changing it to "Both" cannot switch the relationship from OrderDate to ShipDate. Study tip: On the Power BI exam, whenever you see a fact table with multiple date columns, immediately think "one active relationship + USERELATIONSHIP for the exception." That pattern is a recurring theme.

Question 10

In an Import model, Products contains one row per ProductKey, and Sales contains many rows per ProductKey. Several Sales rows contain ProductKey values that are missing from Products because the product load is delayed. The model currently has no relationship between the tables.

How should you configure the relationship, and what result should you expect until the missing products are loaded?

  1. Use many-to-one from Sales to Products with filtering from Sales; Power BI will automatically create physical product rows for unmatched keys.
  2. Use many-to-many between Products and Sales with bidirectional filtering; unmatched sales will be assigned to the closest existing ProductKey.
  3. Use one-to-one between Products and Sales with bidirectional filtering; unmatched sales will be excluded from all model calculations.
  4. Use one-to-many from Products to Sales with single-direction filtering; sales rows with unmatched ProductKey values will be grouped under a blank product member in visuals. (correct answer)
Explanation: When configuring relationships in Power BI's Import mode, you need to understand both cardinality and filter direction — and crucially, what happens to rows that don't match across tables. In this scenario, Products has one row per ProductKey (the "one" side) and Sales has many rows per ProductKey (the "many" side). The correct approach is a one-to-many relationship from Products to Sales, with filters flowing from Products down to Sales — the standard star-schema pattern. When Sales rows contain ProductKey values that don't exist in Products, Power BI doesn't discard them. Instead, it groups all unmatched Sales rows under a blank member in visuals, which is Power BI's default behavior for referential integrity violations. This is answer D, and it's the expected, documented behavior until the missing products are loaded. Answer A is backwards — the "many-to-one from Sales to Products" framing just describes the same relationship from the wrong direction, but the real problem is the false claim that Power BI automatically creates physical product rows. It doesn't; unmatched keys surface as blanks, not auto-generated rows. Answer B introduces a many-to-many relationship, which is incorrect here because Products genuinely has unique ProductKey values — many-to-many is unnecessary and would cause performance and ambiguity issues. Power BI also never assigns unmatched rows to the "closest" key; that concept doesn't exist in the engine. Answer C is wrong on both cardinality (one-to-one requires unique keys on both sides, which Sales doesn't have) and behavior (unmatched rows aren't excluded — they appear as blanks). Remember: In Power BI, referential integrity violations don't cause errors or exclusions by default — they create a blank member. Knowing this blank-member behavior is a reliable signal on exam questions about mismatched keys.