Forum Discussion
Helper IIIWorking with Many to Many doesn't give me correct value.
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.
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.
3 Replies
- Ritaf1983
Super User
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 
Nepal101Hello 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 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.
