Only one active relationship between two tables is supported by Power BI and Power Pivot. Only one relationship can be active at a time, even though you can create multiple relationships between the same pair of tables. When your relationship uses composite keys—that is, several columns combined to identify rows uniquely—this presents problems.
Techniques for Managing Several Matching Keys
1. Make one composite key column.
Combining the separate key columns into a single composite key column in both tables is the most popular and efficient workaround.
Actions to take:
Use DAX or Power Query to add a new column to each table:
CompositeKey = Table[Key1] & "-" & Table[Key2] & "-" & Table[Key3]
Use this new CompositeKey column to create the relationship between the tables.
2. Make Use of a Bridge Table
Consider creating a bridge table that connects to each fact or dimension table and maps the various fields uniquely if you are unable to make a single composite key.
Use case:
There are many too many connections or situations in which a direct connection is impractical.
3. Using DAX to Model Alternatives (USERELATIONSHIP)
If you have more than one relationship (but only one can be active), you can use USERELATIONSHIP() to activate the inactive relationship on a per-measure basis:
Total Amount = CALCULATE(SUM(FactTable[Amount]), USERELATIONSHIP(DimTable[AltKey], FactTable[AltKey]))
When switching between different keys for analysis.
Allows multiple analytical perspectives on the same data.
