Match 2 Columns in 2 tables

[MintuBaruah]

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.

[v-shex-msft · Accepted solution]

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

[Tahreem24]

@MintuBaruah Find the attached PBIX file for your reference.

[MintuBaruah]

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]

@MintuBaruah Create this DAX Column:

Column = IF(TableA[Activity Group]=RELATED(TableB[Activity Group]) && TableA[Activity]=RELATED(TableB[Activity Desc]),RELATED(TableB[Rates]),BLANK())

[MintuBaruah]

Hi @Tahreem24 

Thank you for the reply.

Could you please check the question again as i have added a few rows because i have data similar to the updated data.

I tried the solution but it is showing an error.

Regards.

[Tahreem24]

@MintuBaruah  Then try this:

Join based on Activity column.

Column = IF(TableA[Activity Group]=RELATED(TableB[Activity Group]) && TableA[Activity]=RELATED(TableB[Activity ]),RELATED(TableB[Rates]),BLANK())

[MintuBaruah]

@Tahreem24 , 

I am still getting the error as Activity column also has duplicate data in both the tables.

Thank you

[Arul]

Hi @MintuBaruah ,

Just create the relationship between Activity Group of the tables.

Thanks,

Arul

[MintuBaruah]

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.