Options to relate tables

[Enigma]

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.

[v-lionel-msft · Accepted solution]

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 Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz]

Hi @Enigma 

2 option, power bi is optimised for 1 to many 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn