Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Many to many relationship help

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 

IDInvoice_ID
1301004789970
1301005789970
1301006789970
1301007789970
1301008789970
1301009789970
1301010789970
1301011789970
1301012789970
1301013789970
1301014789970
1301015789970
1301016789970
1301017789970
1301018789970
1301019789970

 

Table 2:

invoice_idbuilding_id
7899705132
7899705133
7899705134
7899705135
7899705136
7899705137
7899705138
7899705139
7899705140
7899705141
7899705142
7899705143
7899705144
7899705146
7899705148

 

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

 

IDInvoice_IDbuilding_id
13010047899705132
13010057899705133
13010067899705134
13010077899705135
13010087899705136
13010097899705137
13010107899705138
13010117899705139
13010127899705140
13010137899705141
13010147899705142
13010157899705143
13010167899705144
13010177899705146
13010187899705148

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies

  • itchyeyeballs's avatar
    itchyeyeballs
    Icon for Impactful Individual rankImpactful Individual
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        itchyeyeballs
        Icon for Impactful Individual rankImpactful Individual
        7 years ago

        Hi from your example:

         

         table 1 

        IDInvoice_ID
        1301004789970

         

        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

  • v-xicai's avatar
    v-xicai
    Icon for Community Support rankCommunity Support
    7 years ago

    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.