Forum Discussion
Helper IIIOptions to relate tables
Hi,
I have three tables in my model. Each of them has 50+ columns. There is a common column called 'Community' in each.
I want to use a single slicer using the Community field and apply on various visuals that reference fields across the three tables.
Could you help me understand the pros and cons of the below two options?
1) Create a many-to-many relationship between all three tables on the Community column
2) Create a separate table 'Community Master' that has only unique values and create relationships from it to all the three tables
Which one should I go for?
P.S. The data in three tables is diverse, so merging tables is not an option.
Hi Enigma ,
“Before relationships with a many-many cardinality became available, the relationship between two tables was defined in Power BI. At least one of the table columns involved in the relationship had to contain unique values. Often, though, no columns contained unique values.
For example, two tables might have had a column labeled Country. The values of Country weren't unique in either table, though. To join such tables, you had to create a workaround. One workaround might be to introduce extra tables with the needed unique values. With relationships with a many-many cardinality, you can join such tables directly, if you use a relationship with a cardinality of many-to-many.”
What a relationship with a many-many cardinality solves
It is more convenient to directly establish a many-to-many relationship.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
v-lionel-msftCommunity Support
Hi Enigma ,
“Before relationships with a many-many cardinality became available, the relationship between two tables was defined in Power BI. At least one of the table columns involved in the relationship had to contain unique values. Often, though, no columns contained unique values.
For example, two tables might have had a column labeled Country. The values of Country weren't unique in either table, though. To join such tables, you had to create a workaround. One workaround might be to introduce extra tables with the needed unique values. With relationships with a many-many cardinality, you can join such tables directly, if you use a relationship with a cardinality of many-to-many.”
What a relationship with a many-many cardinality solves
It is more convenient to directly establish a many-to-many relationship.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
