Conditional/calculated column base on 2 criteria

[Anonymous]

Hello, is it possible to create a conditional column from another table. Im trying to validate using 2 criteria or condition if the employee from table1 exist in table2 on a specific month. Eg. if table1 employee doesnt exist on table2 on month of January it will input 1 as No, and if table1 employee exist on table2 on month of November it will input 1 as yes and 0 as no. Appreciate all your replies!

table1 
EID                                    Month                       Does Exist (conditional col)                Doesn't Exist(conditional col) 

vanessa.escudero              Jan                                0                                                                    1

jayvee.valdez                     Jan                                0                                                                    1

jayvee.valdez                     Nov                               1                                                                    0

charlotte.yoro                    Feb                                0                                                                   1

charlotte.yoro                    June                              0                                                                    1

charlotte.yoro                    Nov                               1                                                                    0

emma.solo                        March                            0                                                                     1

andrea.baral                      June                              0                                                                      1

andrea.baral                      Nov                               1                                                                      0

table2
EID                                    Month

jayvee.valdez                     Nov

charlotte.yoro                    Nov

andrea.baral                      Nov

[v-easonf-msft]

Hi, @Anonymous 

You can add index column for each table in PowerQuery.

Then you can try calculated column like:

Does Exist = 
VAR result =
    LOOKUPVALUE (
        table2[Index],
        table2[EID], table1[EID],
        table2[Month], table1[Month]
    )
RETURN
    IF ( ISBLANK ( result ), 0, 1 )

Doesn't Exist = 
VAR result =
    LOOKUPVALUE (
        table2[Index],
        table2[EID], table1[EID],
        table2[Month], table1[Month]
    )
RETURN
    IF ( ISBLANK ( result ), 1, 0 )

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hello, i've tried this but its giving me an error "A table of multiple values was supplied where a single value was expected."

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

Is this a error you encountered?

You need to make sure there are no duplicate records in table2.

You can also try formula like below:

Does Exist2 =
VAR result =
    CALCULATE (
        MAX ( table2[Index] ),
        FILTER ( table2, table2[EID] = table1[EID] && table2[Month] = table1[Month] )
    )
RETURN
    IF ( ISBLANK ( result ), 0, 1 )

Doesn't Exist2 = 
VAR result =
    CALCULATE(MAX(table2[Index]),
        FILTER(table2,table2[EID]=table1[EID]&&
        table2[Month]=table1[Month]
    ))
RETURN
    IF ( ISBLANK ( result ), 1, 0 )

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.