DP-600: implementing many-to-many relationships in semantic models

I will teach how to correctly implement many-to-many (M:N) relationships in semantic models in the context of the DP-600 exam and in professional practice. This skill is common in real models with different granularities and is important to ensure correct results and acceptable performance.

What you need to know

A many-to-many (M:N) relationship occurs when multiple rows in table A relate to multiple rows in table B. In simple relational models (star schema) we avoid this, but real-world data (e.g.: products with multiple tags, customers in multiple regions, events with multiple participants) generate M:N. In the context of semantic models (Power BI / Fabric), mishandling M:N causes incorrect counts, duplication in aggregations and performance issues.

There are two common approaches:

• Normalized bridge table: a fact-like table that expresses the relationship between the two entities.
• Model with a relationship table and cardinality configured in the semantic model using properties like "Many-to-many (single direction / both directions)" and, occasionally, distinct count columns to control aggregations.

Practical example: we have a Product table and a Tag table. A product can have several tags and a tag can apply to several products. If we join Product and Tag directly without a bridge table, we will duplicate metrics (e.g.: Sales) for each associated tag.

How it works

Conceptual steps to correctly implement an M:N scenario using a bridge table:

1. Identify the tables involved and the nature of the relationship (A ⇄ B).
2. Create a bridge table that contains key pairs: ProductID, TagID. This table should not contain factual metric values that would be duplicated.
3. Establish 1:N relationships between Product → Bridge (1 side is Product) and Tag → Bridge (1 side is Tag). In the semantic model, both relationships should be one-to-many (1:N), with the Bridge on the many side.
4. Configure filter directionality: generally use Single direction from the dimensions to the Bridge or Both directions when you need to propagate filters through the bridge to facts, but Both directions can affect performance and introduce ambiguity. Prefer Single and explicit measures when possible.
5. Write DAX measures that avoid duplication: when aggregating facts through the bridge, use functions like DISTINCT, SUMX over values aggregated by key, or measures that calculate over the original fact table and then relate to the bridge without duplicating.

-- Exemplo DAX (padrão) para somar Sales por Tag sem duplicar quando Product tem várias tags
Sales by Tag =
VAR ProductsForTag =
    DISTINCT( Bridge[ProductID] )
RETURN
    CALCULATE(
        SUM( Sales[SalesAmount] ),
        KEEPFILTERS( ProductsForTag )
    )

Alternative with SUMX to ensure a unique summation per product:

Sales by Tag 2 =
SUMX(
    VALUES( Bridge[ProductID] ),
    CALCULATE( SUM( Sales[SalesAmount] ) )
)

In practice

Step-by-step example in a Fabric / Power BI Desktop environment:

1. In Data Factory / Power Query, create the Bridge table with ProductID-TagID pairs. Do not import unnecessary columns that increase cardinality.
2. In Model view, link Product[ProductID] → Bridge[ProductID] (1 → *), and Tag[TagID] → Bridge[TagID] (1 → *).
3. Set both relationships to Single direction by default. Only use Both directions if there is a clear need for bidirectional filtering (e.g.: slicers that need to cross both dimensions through the Bridge).
4. Create measures in the model measure space that use VALUES/DISTINCT to avoid duplication. Test with cards and tables to confirm that sums match the expected total.
5. Monitor cardinalities and compression: in Fabric / Power BI, tables with high cardinality in the bridge increase the model; evaluate whether the bridge can be reduced (for example, using smaller integers, removing unnecessary columns, or aggregating).

Common mistakes

• Duplication of facts when aggregating without using DISTINCT/VALUES or without a normalized bridge — causes inflated totals.
• Using Both directions automatically and without assessment — it may solve filters but introduce relationship loops, ambiguities and degrade performance.
• Placing metrics in the bridge (e.g.: SalesAmount) instead of keeping them in the fact table — increases cardinality and makes correct calculations harder.

How to practice

Practice with a dataset that contains M:N relationships (for example: Products, Tags, Sales). Implement the bridge and create measures that confirm the totals. For official evaluation, use the Microsoft OFFICIAL Practice Assessment (free) and follow the Microsoft study guide (free) for DP-600. Those resources are the only ones that reproduce the format and types of exam questions; use them to validate knowledge and identify gaps.

In summary

• Use a bridge table to normalize M:N relationships and avoid duplication of facts.
• Prefer Single direction in relationships and write DAX measures with DISTINCT/VALUES or SUMX for correct summations.
• Assess cardinality and performance: reduce unnecessary columns and types in the bridge.
• Practice with real datasets and validate with the official Practice Assessment and the Microsoft study guide (both free).