DAX: VLOOKUP in IF statement

I have 2 tables in PBI with a many-to-many relationship (i know it's not ideal but it makes sense given the data).

I am trying to calculate a new column in table 1 based on some values that are in table 2. The relationship is through the key id in both.

I'm trying to build a condition like

> if ((table 1[id]) has (table 2[code]) = xxxx, "XXXX", "else").

I tried with

> IF(LOOKUPVALUE(table2[code], (table1[id]), table2[id]) = "xxxx", "XXXX", "else")

but that's not working.

From my understanding LOOKUPVALUE should be something similar to VLOOKUP...

I also looked into RELATED and RELATEDTABLE but I don't see how to code this in DAX

Solution

Use CALCULATE with FILTER to have more flexible lookup in a many-to-many relationship by filtering directly on Table 2 based on your condition :

NewColumn = 
IF(
    CALCULATE(
        COUNTROWS(Table2),
        Table2[code] = "xxxx",
        Table2[id] = Table1[id]
    ) > 0,
    "XXXX",
    "else"
)