Forum Discussion
Many to Many, both direction relationship
Hi there, I'm trying to make relationships in my report and it suggests me many to many and both direction.
Question.,
1) when pbi ask to make 'many to many and both direction' relationship?
2) is there any ways to avoid making this condition relationship because i know that if i do, the report ends up on tricky sitution or the value would be funny.
Regards,
CL
HI Anonymous
Question.,
1) when pbi ask to make 'many to many and both direction' relationship?
When the columns used for matching do not have unique values on both tables.
2) is there any ways to avoid making this condition relationship because i know that if i do, the report ends up on tricky sitution or the value would be funny.
Use the columns which have unique values in atleast one of the table
4 Replies
- Greg_Deckler
Community Champion
Anonymous - Yes, you should avoid those if at all possible. Instead, create a bridge table of distinct values for those columns and use that bridge table in your visualizations. You can create a bridge table like so:
Bridge Table = DISTINCT(UNION(SELECTCOLUMNS('Table1',"Key",[Key]),SELECTCOLUMNS('Table2',"Key",[Key])))
- amitchandak
Super User
Anonymous , I am assuming you want Tag register to be on one side.
1. It should not have null/blank values.
2. If yes convert then to some value like n/a using power query replace option.
https://www.howtoexcel.org/power-query/bulk-replace-values/
3. Do the above for M -side table.
4. 1 side table should have all unique value.
5. Plot tag number and count of tag number from Tag register table on a visual and filter for the count of Tag number > 1. You should not get any rows.
6. if you have duplicate rows. delete duplicate in edit query
https://backtosql.wordpress.com/2019/04/19/the-easy-way-to-duplicate-tables-in-power-bi/
7. In case both are M to M and you want to keep them like that. Use bridge table
https://www.seerinteractive.com/blog/join-many-many-power-bi/
8. How to use distinct to that, check in the file
https://www.dropbox.com/s/op9lb78w9utdonz/Distinct%20from%20two%20Tables.pbix?dl=0
HI Anonymous
Question.,
1) when pbi ask to make 'many to many and both direction' relationship?
When the columns used for matching do not have unique values on both tables.
2) is there any ways to avoid making this condition relationship because i know that if i do, the report ends up on tricky sitution or the value would be funny.
Use the columns which have unique values in atleast one of the table
Anonymous
In simple word, columns with repeated values are on many side. In your case, you can create another bridge table with unique tag No, then let both tables connect to the new table.new table = Distinct(Tag Register[Tag No])
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
