VLOOKUP with Unmatched Criteria

[aarontanek]

Hi,

I have 2 tables.  Table 2 is replicated from Table 1 with the below sample data:

Table 1		Table 2
COL 1	COL 2	COL 1	COL 2	COL 3
123	A	123	A	X
123	B	123	B	Y
456	A	456	A	Z

What I would like to achieve is to vlookup Table 2 with the below criteria:

T1.C1 = T2.C1 and T1.C2 <> T2.C2

End result is as per below in column 3.

Table 1
COL 1	COL 2	COL 3
123	A	Y
123	B	X
456	A

Is this possible in DAX?

Thank you.

[Cookistador · Accepted solution]

Hi @aarontanek 

The following dax code should return what you need

Lookup =
VAR current_col1_T1 = Table1[COL 1]
VAR current_col2_T1 = Table1[COL 2]
VAR matching_rows_in_Table2 =
FILTER (
'Table 2',
'Table 2'[COL 1] = current_col1_T1 &&
'Table 2'[COL 2] <> current_col2_T1
)
RETURN
MAXX(matching_rows_in_Table2, 'Table 2'[COL 3])

[aarontanek]

It works! Thank you so much!