Star schema relationships in Power BI are one‑to‑many (1:), many‑to‑one (:1) and properly configured many‑to‑many (:) using bridge tables or composite models. Handle many‑to‑many safely by isolating ambiguous joins with a dedicated bridge table and using USERELATIONSHIP/TREATAS in measures.
Steps to model a safe many‑to‑many
1. Identify the two grain tables (e.g., Sales and Products) that need a many‑to‑many link.
2. Create a bridge table that contains the distinct keys from both sides, e.g.:
SELECT DISTINCT
Sales[ProductKey] AS ProductKey,
Products[CategoryKey] AS CategoryKey
INTO dbo.Bridge_ProductCategory
FROM Sales
JOIN Products ON Sales[ProductKey] = Products[ProductKey];3. Set inactive relationships (cross‑filter direction = Both) from Sales→Bridge and Products→Bridge.
4. In any measure that requires the relationship, activate it with USERELATIONSHIP:
Total Sales by Category :=
CALCULATE (
SUM ( Sales[Amount] ),
USERELATIONSHIP ( Sales[ProductKey], Bridge_ProductCategory[ProductKey] ),
USERELATIONSHIP ( Products[CategoryKey], Bridge_ProductCategory[CategoryKey] )
)5. For ad‑hoc filters, prefer TREATAS to avoid extra relationships:
Sales for Selected Categories :=
CALCULATE (
[Total Sales],
TREATAS ( VALUES ( Products[CategoryKey] ), Bridge_ProductCategory[CategoryKey] )
)6. Validate cardinality: the bridge must not exceed ~1 million rows; larger sets should be partitioned or filtered at source.
Quick comparison
| Approach | Cardinality control | Performance impact | Recommended use |
|----------|--------------------|--------------------|-----------------|
| Inactive relationships + USERELATIONSHIP | High | Medium (single activation) | Standard many‑to‑many |
| TREATAS only | Very high (no extra relationships) | Low | Dynamic slicers |
| Composite model (direct query) | Depends on source | Variable | When source already enforces uniqueness |
Follow these steps and use the table to decide which technique fits your model size and refresh budget.