Forum Discussion

MintuBaruah's avatar
MintuBaruah
Icon for Helper III rankHelper III
4 years ago
Solved

Match 2 Columns in 2 tables

Hi @all

 

I have 2 tables, Table A and Table B.

The condition is to match 2 columns (ie, Activity Group and Activity) from Table A with 2 columns (ie, Activity Group and Activity Desc) of Table B, so that I can get the data from the third column in Table B.

For eg: 

 

Table A:

Activity Group Activity
Internal Admin Internal Admin
Fund Account NAV
Fund Account Invest
Fund Account Internal Admin

 

Table B: 

Activity Group Activity  Rates
Internal Admin Trades B
Fund Account NAV C
Fund Account  Report A
Internal Admin Invest B

 

In the above tables, 

Fund Account from Table A Matches with Table B, So it should fetch C from Rates Column.

 

Please help how to solve this in power bi.

 

Thank you.

Need Help

9 Replies

  • Arul's avatar
    Arul
    Icon for Super User rankSuper User
    4 years ago

    Hi MintuBaruah ,

     

    Just create the relationship between Activity Group of the tables.

     

    Thanks,

    Arul

    • MintuBaruah's avatar
      MintuBaruah
      Icon for Helper III rankHelper III
      4 years ago

      Hi Arul 

       

      Thank you for the reply.

       

      I have tried that but by doing that It will also fetch B from Table B because Internal Admin matches. 

      The conditition is to fetch Rates only if Both Activity Group and Activity columns matches with the other table.

       

      Regards.

    • MintuBaruah's avatar
      MintuBaruah
      Icon for Helper III rankHelper III
      4 years ago

      Hi Tahreem24 

       

      This is not correct as it is showing B also in Rates.

      Conditition: fetch Rates only if Both Activity Group and Activity columns matches with the other tables Activity Group and Activity.

       

      Thank you.

      • Tahreem24's avatar
        Tahreem24
        Icon for Super User rankSuper User
        4 years ago

        MintuBaruah Create this DAX Column:

        Column = IF(TableA[Activity Group]=RELATED(TableB[Activity Group]) && TableA[Activity]=RELATED(TableB[Activity Desc]),RELATED(TableB[Rates]),BLANK())
         
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi MintuBaruah,

    I'd like to suggest you take a look at the following link about creating relationships between many columns:

    Relationship in Power BI with Multiple Columns - RADACAD

    You can create a calculated column concatenate these two column values and extract them to create a new clause table as the bridge to link two tables based on the merge field values.

    How to Join Many to Many with a Bridge Table in Power BI | Seer Interactive

    After these steps, you can simply get the related field value based on the relationship keys.
    Regards,

    Xiaoxin Sheng