Working with Many to Many doesn't give me correct value.

[Nepal101]

Hello Everyone, 
I was trying to create a table by merging two different tables in one but I am getting duplicate rows for dates. I tried to create a bridge table instead of merging the two tables then there was an error saying Can't determine the relationship between fields. 
Merging the two tables below is an issue where the date gets duplicated. 

when I tried to create the bridge table 

but then I got an error saying 

Please help if there is any solution or workaround for solving this issue. 
Thank you for your time. 

[Anonymous · Accepted solution]

Hi @Nepal101 ,

Thanks @Ritaf1983  for the quick reply. I have some additional suggestions:

(1)Add a column to Table 1.

rank = RANKX(FILTER('Table (1)',[Client name ]=EARLIER('Table (1)'[Client name ]) ),[Communicationdate ],,ASC,Dense)

(2)Add a column to Table 2.

rank = RANKX(FILTER('Table (2)',[client name ]=EARLIER('Table (2)'[client name ])),[Sample provided date],,ASC,Dense)

(3)We can create a table.

NewTable =
SELECTCOLUMNS (
    ADDCOLUMNS (
        'Table (1)',
        "s_date",
            CALCULATE (
                MAX ( 'Table (2)'[Sample provided date] ),
                FILTER (
                    'Table (2)',
                    [Client name ] = EARLIER ( 'Table (1)'[Client name ] )
                        && [code] = EARLIER ( 'Table (1)'[code] )
                        && [rank] = EARLIER ( 'Table (1)'[rank] )
                )
            )
    ),
    "Client name", [Client name ],
    "Communicationdate", [Communicationdate ],
    "Sample Provided date", [s_date]
)

Best Regards,

Neeko Tang

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

[Nepal101]

Hello @Ritaf1983 Thank you for the suggestion, Below is the sample data where each data is coming from a different database. 

Client name	Communicationdate	code
Automotive	null	4018
Automotive	3/19/2024	4018
Automotive	4/15/2024	4018

client name	Sample provided date	Empcode
Automotive	1/5/2023	4018
Automotive	3/25/2024	4018
Automotive	4/16/2024	4018

The result that I need is shown below, The above two tables have many to many relationships. I tried to merge the table and it gave me a duplicate date(on the screenshot) for each row. so I was thinking of creating a bridge table that also gave me an error (in the above screenshot) Can you please help me with any suggestion.

Client name	Communicationdate	Sample Provided date
Automotive	null	1/5/2023
Automotive	3/19/2024	3/25/2024
Automotive	4/15/2024	4/16/2024

[Ritaf1983]

Hi @Nepal101 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523