Merge a table 2 times to another table to get 2 different columns

[monishd]

Is there a more straightforward way to merge two tables two times with different columns and get new columns based on the match? Can I achieve the below result in one merge or will I have to go for two merge?

I have a table 1:

Item	Opened	Closed
I1	XYZ	XYZ
I2	ABC	XYZ
I3	PQR	PQR
I4	PQR	XYZ

table 2:

Employee	Type
ABC	Manual
PQR	System
XYZ	System

The new table I want should be like:

Item	Opened	Opened By	Closed	Closed By
I1	XYZ	System	XYZ	System
I2	ABC	Manual	XYZ	System
I3	PQR	System	PQR	System
I4	PQR	System	ABC	Manual

[Irwan · Accepted solution]

hello @monishd 

please check if this accomodate your need.

based on the result table your show, i think you want to find a 'Type' value based on matched 'Employee' value (not table merge).

But please correct me if i am wrong (since i am not sure why I4 has ABC in 'ByClosed' instead of XYZ as shown in 'Table 1').

1. in your table 1, create a calculated column for 'Opened by' with following DAX.

Opened by = 
MAXX(
    FILTER(
        'Table 2',
        'Table 1'[Opened]='Table 2'[Employee]
    ),
    'Table 2'[Type]
)

2. again, create a calculated column for 'Closed by' with following DAX.

Closed by =
MAXX(
    FILTER(
        'Table 2',
        'Table 1'[Closed]='Table 2'[Employee]
    ),
    'Table 2'[Type]
)

Hope this will help.

Thank you.