Lookupvalue from another table

[xRTP]

Hello, is it possible to create a conditional lookupvalue from another table. Im trying to validate if the employee from table1 exist in table2, if it exist then it input a 1 as yes and 0 as no. Appreciate all your replies thanks!

table1

vanessa.escudero

jayvee.valdez

charlotte.yoro

emma.solo

andrea.baral

table2

andrea.baral

charlotte.yoro

jayvee.valdez

[amitchandak]

@xRTP , You can create a flag like this

in table 1

a new column =

var _cnt = countx(filter(Table1, Table1[employee] = table2[Employee]) , Table2[Employee])

return

if(isblank(_cnt), "No", "Yes")

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[xRTP]

Hello, it worked! but where to add a another filter for date? Since in my table there is a month column. What happens is it marked as yes because it exists on the other table but that was for different month. Even if the table1 have the employee and doesnt exist on table2 for the month of November it is print yes.

[v-rongtiep-msft · Accepted solution]

Hi @xRTP ,

Please have a try.

Create a measure.

measure =
VAR _1 =
    CALCULATE (
        COUNT ( table2[month] ),
        FILTER (
            ALL ( table1 ),
            table1[employee] = SELECTEDVALUE ( table2[employee] )
                && table1[month] <> SELECTEDVALUE ( table2[month] )
        )
    )
RETURN
    IF ( _1 < 1, "No", "Yes" )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xRTP]

It works but there are still some discrepancy with numbers. Thanks a lot! 🙂