Many to many relationship help

[Anonymous]

Hi everyone,

I have two table connected with many to many relationship 😞 

I need building_id from table 2 in table 1. Is it possible to get result like below.

 table 1 

ID	Invoice_ID
1301004	789970
1301005	789970
1301006	789970
1301007	789970
1301008	789970
1301009	789970
1301010	789970
1301011	789970
1301012	789970
1301013	789970
1301014	789970
1301015	789970
1301016	789970
1301017	789970
1301018	789970
1301019	789970

Table 2:

invoice_id	building_id
789970	5132
789970	5133
789970	5134
789970	5135
789970	5136
789970	5137
789970	5138
789970	5139
789970	5140
789970	5141
789970	5142
789970	5143
789970	5144
789970	5146
789970	5148

I want new column in table 1 with building id's like below

ID	Invoice_ID	building_id
1301004	789970	5132
1301005	789970	5133
1301006	789970	5134
1301007	789970	5135
1301008	789970	5136
1301009	789970	5137
1301010	789970	5138
1301011	789970	5139
1301012	789970	5140
1301013	789970	5141
1301014	789970	5142
1301015	789970	5143
1301016	789970	5144
1301017	789970	5146
1301018	789970	5148

[v-xicai]

Hi @Anonymous ,

As itchyeyeballs said above, table1[ID] will match to every row in table 2 .

When you create calculated column building_id _matched in table2 , return result showing picture below.

building_id _matched = CALCULATE(FIRSTNONBLANK(Table2[building_id],1),FILTER(ALLSELECTED(Table1),Table1[Invoice_ID]=Table2[invoice_id]))

Best Regards,

Amy

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

[itchyeyeballs]

Its hard to figure out what you are trying to achieve from your description, you can do a many to many join in the data import phase rather than in the model if you just want to end up with a single table with duplicated rows for all the matches.

[Anonymous]

Hi @itchyeyeballs  This is the problem, I don't want duplicates row's. I understand if it's many to many join, I always gonna end up with duplicates but might be something there in DAX which could give me row's without duplicates. If you look at the table 3 in the bottom, it has no duplicate row that's my requirement.

[itchyeyeballs]

Hi from your example:

 table 1 

ID	Invoice_ID
1301004	789970

will match to every row in table 2 if joined on invoice_id. how do you want to identify which rows to keep?

There is a summarize function in DAX that may be of use but without more context its hard to tell