Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Enigma
Helper III
Helper III

Options 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.

1 ACCEPTED SOLUTION
v-lionel-msft
Community Support
Community 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 Chen

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

View solution in original post

2 REPLIES 2
v-lionel-msft
Community Support
Community 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 Chen

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

Mariusz
Community Champion
Community Champion

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.