Many to Many Relatioship

[Anonymous]

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

[Anonymous]

Can I get any help on this please

[v-kelly-msft]

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,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hello Kelly

I have created bridge table but it didn't work as expected

[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,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi  @v-kelly-msft 

Thanks for helping, i will give you   below data  sample please refer,

error table sample

DUPLICATE FIELD	CUSTODIAN	CLIENT	CREATE DATE
Duplicates	GBS CDM Atlanta	North America	4/1/2020
Street	GBS CDM Atlanta	North America	3/1/2020
Street	GBS CDM Atlanta	North America	3/1/2020
Name1	GBS CDM Atlanta	North America	3/1/2020
Name1	GBS CDM Atlanta	North America	3/1/2020

Client Record Table

Group Type	Group Name	Record Count	CREATE DATE
Client	Corporate	3	4/1/2019
Client	CPS EEA	3	4/1/2019
Client	CPS Asia	2	4/1/2019
Client	CPS EEA	1	3/1/2020
Client	CPS Latin America	4	4/1/2019
Client	CPS Latin America	2	4/1/2019
Client	CPS Latin America	2	4/1/2019
Client	Corporate	1	9/1/2019
Client	Corporate	1	9/1/2019
Client	Corporate	1	1/1/2020
Client	Corporate	1	3/1/2020
Client	Corporate	1	3/1/2020
Client	CPS Asia	2	4/1/2019
Client	CPS Asia	1	4/1/2019

I need 1 date slicer to filter output  and output table like below

Client Type	Count of Duplicate	Total Record	Duplicate percentage
Corporate	5	478	0.01
CPS Asia	4	230	0.01
CPS EEA	2	140	0.01
CPS Latin America	1	131	0.01
CPS North America	0	111	0.01
EMEA	4	110	0.05
Latin America	3	990	0.05
North America	7	56	0.05
Not Aligned	1	120	0.05
Pacific	4	160	0.01

Currently i getting wrong Total Reord in my output table

Delete

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Hi Darlove

Please see this screeshot , this is  actual table need to connect 

[amitchandak]

@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