Power BI Many-to-Many Interview Questions: Bridge Tables and Ambiguous Filters

A Power BI matrix shows Red segment sales of $180 and Blue segment sales of $160. The grand total is $240. Is the measure broken? A strong interview answer does not immediately rewrite DAX. It asks what one row of the sales table represents, whether a customer can belong to both segments, and which relationship path applies the segment filter.

Microsoft's many-to-many relationship guidance treats a bridge of associations as a common way to model membership between two dimensions and explicitly warns that totals can be nonadditive. The customer and dollar values below are an original practice case, not a candidate report or a claim about a particular employer's interview. No verified same-cycle candidate report establishes that any company asks this exact set. The goal is to make your modeling explanation testable, from source grain through visual result; the related duplicate-key join exercise is useful preparation for that distinction.

Question 1: What exactly is many-to-many in this model?

Start by naming three grains. Customer has one row per CustomerID. Sales has one row per transaction, each with a CustomerID and amount. CustomerSegment has one row per customer-segment membership, so a customer may occur more than once there. Segment has one row per segment. The conceptual shape is Segment (1) → CustomerSegment (*) → Customer (1) → Sales (*); the arrows here describe the desired selection flow, not a claim that every relationship's default cross-filter setting points in that direction.

The membership table is a bridge, not another sales fact. In Microsoft's dimensional guidance, the bridge can carry pairs of keys while each dimension keeps a unique key. This avoids pretending that the customer-to-segment association is one-to-one. A direct many-to-many relationship between two nonunique tables is possible in the product, but it does not solve ambiguous grain by itself. In particular, joining two fact-like tables by an under-specified key can multiply rows or make a measure's meaning hard to defend.

Ask what the interviewer means by “many-to-many.” Is it customer-to-segment membership, a fact-to-fact comparison, or two tables whose keys only appear unique in a small sample? Before choosing a relationship cardinality, run a duplicate count on the proposed one-side key, inspect blank keys, and decide whether the association key pair should be unique. The model's cardinality setting should reflect the data contract, not be used to silence an error about dirty keys.

For this case, make CustomerID unique in Customer, SegmentID unique in Segment, and (CustomerID, SegmentID) unique in the bridge. A customer may still have multiple bridge rows because the segment differs. If a bridge row is duplicated exactly, it is a quality defect worth detecting even if a distinct-customer measure hides its effect in one chart. If membership has effective dates, the real key may include a time interval; a timeless bridge is then insufficient for historical reporting.

Question 2: Why is the total $240 instead of $340?

Use a tiny fixture and calculate it by hand before opening the visual. In this invented dataset, each customer has a single summarized sales amount for simplicity. The bridge assigns C1 to two segments.

Customer	Sales	Segment memberships
C1	$100	Red, Blue
C2	$80	Red
C3	$60	Blue

Red selects C1 and C2, so its sales are $180. Blue selects C1 and C3, so its sales are $160. Across both selected segments, the distinct customer set is C1, C2, C3 and sales are $240. Adding $180 and $160 instead counts C1's $100 twice, yielding $340. That arithmetic is not a DAX bug. It is the consequence of overlapping membership. A measure can be additive across disjoint transaction partitions while not additive across overlapping segment sets.

An interview answer should say what the grand-total cell computes: the sales measure is reevaluated under the grand-total filter context. It is not necessarily a sum of the visible row cells. This follows the behavior documented in Microsoft's many-to-many guidance. Whether the business wants the unique $240 total, an explicitly allocated total, or a blank grand total is a product question. If the request is “make row values sum to the total,” ask for an allocation rule. For example, splitting C1's $100 evenly gives Red $130 and Blue $110, whose sum is $240, but that is a different metric with a declared 50/50 attribution policy. Do not silently replace the original sales measure with that allocation.

This fixture is a useful interview asset because it exposes a common false fix: SUMX(VALUES(Segment[SegmentID]), [Sales]) would deliberately sum segment cells and return $340 at the grand total. It can answer “sum of segment-attributed sales,” but it does not recover unique sales. Define the question before altering the measure.

Question 3: How does a segment slicer reach the Sales table?

Trace the filter one relationship at a time. A Segment slicer first selects rows in CustomerSegment. Those memberships must select the matching Customer rows; then Customer filters Sales through its ordinary one-to-many relationship. Depending on how the two bridge relationships are arranged, the bridge-to-customer step may require bidirectional cross-filtering or a measure-level alternative. Microsoft's bidirectional filtering guidance describes the dimension-to-dimension bridge pattern and cautions against applying bidirectional relationships indiscriminately.

Do not answer “turn on Both everywhere.” That can expose multiple paths between tables, surprise report authors, and make performance harder to reason about. A better answer draws the graph, marks active relationships and direction, and chooses the minimum propagation needed for the report. If only one measure needs an exceptional path, a measure-level CROSSFILTER can alter direction within that calculation; the model-wide implications differ from changing a relationship globally. Confirm the exact DAX in the actual model rather than assuming a formula copied from another schema will work.

For a practical test, place Segment[Name], a count of bridge memberships, a distinct count of customers, and [Sales] in the same matrix. Filtering Red should show two memberships and two customers; filtering Blue should also show two and two. If membership changes but sales does not, follow the path from bridge to customer to sales. If all three change but totals seem high, inspect overlap before changing direction. This staged probe distinguishes a blocked filter path from a mathematically correct nonadditive total.

The relationship behavior reference also matters for inactive relationships and multiple paths. An active relationship is not a guarantee that every desired slicer reaches every measure through the route you imagined. Conversely, a filter path that works in a simple test may behave differently when another dimension is added. Explain the whole graph, not just one relationship dialog.

Question 4: Is the bridge duplicating sales?

“The total is too large” has at least three distinct diagnoses. They require different repairs. Do not equate every many-to-many symptom with duplicate transactions.

Observation	Likely cause to test	Next check
Segment rows overlap, unique grand total is lower than their sum	Legitimate shared membership	List customer IDs in each segment; compute intersection
A segment result doubles after a new bridge load	Duplicate (CustomerID, SegmentID) association or an incorrect join	Group the bridge by both keys and count rows; compare before/after load
Every segment row shows the same sales number	Segment selection is not reaching the fact measure	Trace relationship directions and active path
A fact-to-fact join multiplies individual transactions	Key is not unique at the joined grain	Show input and joined row counts by key
A measure changes after another dimension is added	Multiple propagation paths or changed evaluation context	Draw the model graph and reduce to a minimal visual

The table is a diagnostic aid, not an assertion that each symptom has a single cause. In a semantic model, a duplicate bridge row may not numerically double a simple sales measure if the final filter is a set of customer IDs; a SQL materialization that physically joins sales to bridge could double rows. State which computation is happening. Similarly, missing CustomerID values can land in an unknown/blank member or disappear from a particular visual, producing a mismatch that has nothing to do with legitimate overlap. Reconcile source sales by transaction ID before inferring a relationship fix.

The stronger follow-up is temporal: suppose C1 moves from Blue to Green tomorrow. If the business asks for “sales by segment as of the transaction date,” today's static association bridge will rewrite history in every report refresh. You need a time-aware membership design with valid-from/valid-to logic, a snapshot at reporting grain, or another explicitly defined attribution method. That modeling choice is an inference from the business requirement; Microsoft does not prescribe one universal historical bridge schema for every organization.

Question 5: What makes a filter path ambiguous?

Draw a diamond: Segment can reach Sales through Customer, but perhaps another active relationship also connects Segment to a sales classification table that reaches Sales. Now there are competing routes. Microsoft's relationship guidance documents propagation paths, priorities, and ambiguity handling; do not claim that Power BI simply takes the shortest route or that every diamond necessarily throws an error. The concrete behavior depends on cardinality, directions, and path priority. The interview-ready point is to identify paths before predicting a number.

Reduce the model to the smallest reproducer. In a copied report page, remove unrelated visuals, add a table with IDs from both sides, and test one slicer at a time. Check whether the path you intended is active, whether the join key is unique where expected, and whether a measure uses USERELATIONSHIP, CROSSFILTER, or TREATAS. Then consider design repairs: a cleaner star schema, a single-purpose bridge, one active route with measure-specific alternatives, or separate measures for genuinely different definitions. Document why each route exists. The answer is not to stack more bidirectional relationships until the number looks right.

Ask one more question about security. Row-level security is a separate contract from ordinary visual cross-filtering. The relationship-management documentation distinguishes bidirectional filtering from the option to apply security filtering in both directions. Test the intended RLS role with a user who belongs to two segments and with a user who belongs to none. Verify both allowed rows and denied rows in the service context. Never assume that because a slicer filters a measure, an RLS policy propagates identically.

A two-minute answer you can defend

“I would define Customer, Segment, membership, and Sales grains first. The membership bridge contains one row per customer-segment pair; Sales remains one row per transaction. I would trace whether a Segment filter can select membership, then Customer, then Sales, using only the relationship direction or measure-specific override required. In the sample, C1 belongs to Red and Blue, so Red $180 plus Blue $160 counts C1 twice; the unique grand total is $240. I would test duplicate bridge pairs separately from this legitimate overlap, and I would draw any second filter route before changing relationship settings. Finally, I would validate RLS and historical membership according to the actual business contract.”

If the interviewer presses for a quick fix, ask whether they want unique sales, allocated sales, or a sum of segment appearances. Those can all be useful, but they are not interchangeable. A strong analyst labels the chosen measure so a dashboard reader cannot mistake one for another. That is more persuasive than a formula that happens to produce the requested number once.

Sources and further reading

• Microsoft Learn: Many-to-many relationship guidance — bridge modeling and nonadditive totals.
• Microsoft Learn: Bidirectional relationship guidance — filter propagation and when to avoid model-wide Both.
• Microsoft Learn: Model relationship behavior — active relationships, path priority, and ambiguity.
• Microsoft Learn: Create and manage relationships — relationship configuration and security-filter options.