Calculated Column to return value from other table with condition

[Anonymous]

Table 1

ID	Newcolumn
1
2
3

Table 2

ID	Author	Area
1	John	UK
1	Mary	CAN
1	Rob	US
2	Rob	CAN
2	John	US
3	Mary	US
3	John	CAN
3	Rob	US

Create a calculated column in Table 1 to bring in the value from Table2 where Author = "John"

Expected result

ID	NewColumn
1	UK
2	US
3	CAN

DAX expression? I have the model with direct query so cant use LOOKUPVALUE function.

Please help...Appreciate your help...Thank you

[Greg_Deckler]

Well, probably could do something like:

Column = 
VAR __id = [ID]
RETURN
MAXX(FILTER(ALL('Table 2'),'Table 2'[ID] = __id && [Author] = "John"),[Area])

Something like that...

[Anonymous]

Thank you Greg for the solution. As our requirement slightly changed, we ened up creating a measure that returns the same value.

Appreciate you help..