Add custom column

[sayang194]

I have 2 tables as defined below.

Table 1:

ID	Date	Dept
1	2022/02	A
2	2022/02	A
3	2022/02	B
4	2022/02	B
1	2022/03	A
2	2022/03	A
3	2022/03	B
4	2022/03	C
1	2022/04	C
2	2022/04	A
3	2022/04	D
4	2022/04	C

Table 2

ID	Date
1	2022/04
2	2022/03
3	2022/02
4	2022/04

So, I want to add Dept column in Table 2 such that it matches with ID & Date of Table 1. For example if I add a new Dept column for Table 2 it will be like:

ID	Date	Dept
1	2022/04	C
2	2022/03	A
3	2022/02	B
4	2022/04	C

Any idea how I can achieve this?

[johnt75 · Accepted solution]

Dept =
var currentID = 'Table 2'[ID]
var currentDate = 'Table 2'[Date]
return LOOKUPVALUE( 'Table 1'[Dept', 'Table 1'[ID], currentID, 'Table 1'[Date], currentDate)