Forum Discussion
Many to Many Relatioship
Hello All
I'm Trying to create a report where i have two Table as Error Table and Client Record. Both table have Many to Many Relation and Both as cross Filter Drection . I have tried creating Bridge Table and filter total record and total error from Client REcord Table and Error by date but not getting correct numer of total Record. i also used crossfilter dax , but it didnt work Please help me what approach i can take
9 Replies
v-kelly-msftCommunity Support
Hi Anonymous ,
You'd better ceate a bridge table when use many to many relationship,such as below:
Here is the reference. https://docs.microsoft.com/en-us/power-bi/desktop-many-to-many-relationships
Best Regards,
KellyDid I answer your question? Mark my post as a solution!Hi v-kelly-msft.
What you are referring to is not really Many-to-Many. It's an example of a granularity problem.
So it's not the pattern of M2M:
Table1 1:* Bridge *:1 Table2
but a completely different one:
Table1 *:1 DistinctValues 1:* Table2.
This is not Many-to-Many. It's a granularity issue and can be solved easily without the middle table. You can join directly the fields from Table1 to Table2. You then get something that's known as a weak relationship and is enough to solve the problem.
Best
D- v-kelly-msft
Hi Anonymous ，
I made 2 sample tables which have many to many relationship,see below:
Would you pls advise me what is your expected output?
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
- Many-to-Many means having a bridge table from a dimension to a dimension. So, it's Dim1 1:* Bridge *:1 Dim2. The classic example of this is the association between accounts and customers. One account can have several owners and one owner can own several accounts. Therefore the structure is:
Account 1:* AccountToCustomerBridge *:1 Customer.
You do not have a bridge table in your picture.
Best
D- amitchandak
Super User
Anonymous , Your screenshot is not making it clear. Can you share sample data and sample output.
Are you able to join in the required column? Or do need to create a concatenated column.
Like New Key = [Key1] &"-"& [Key2]
Also, refer :
https://www.seerinteractive.com/blog/join-many-many-power-bi/
https://docs.microsoft.com/en-us/power-bi/desktop-many-to-many-relationships
