DAX formula compatibility in DirectQuery PowerBI- Match Value from another table

1
votes

I have two tables where they are connected by Table1[ColA] &Table1[ColB].

Now I am trying to match the values from Table1[ColB] with Table2[ColB] & return the result in Table2[ColC]. Where the result should be -

if it matches "Found"
doesn't match "Not-Found"
else Empty

The Key is to use Use the LOOKUPVALUE function to see, if the value exists.

Now I can use the following query for the output

Col_C = 
         Var out1 = LOOKUPVALUE(Table2[ColB],Table2[ColB],Table1[ColB])
         Var out2 = IF(out1 = "", "Not Found","Found")
         Var out3 = if(Table1[ColB] = "", "Empty", out2)
return out3

But when the data is DirectQuery it seems like LOOKUPVALUE is not supported.

I found one article on Microsoft site saying the DAX formula compatibility in DirectQuery.

Do anyone knows how to query this output or replacement of LOOKUPVALUE in DirectQuery

1
powerbidaxpowerquerym

1 Answers

1
votes

Maybe try IN VALUES instead of LOOKUPVALUE = "":

Col_C =
SWITCH (
    TRUE (),
    ISEMPTY ( Table1[ColB] ), "Empty",
    Table1[ColB] IN VALUES ( Table2[ColB] ), "Found",
    "Not Found"
)