Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Conditional/calculated column base on 2 criteria

 
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

Interesting Links
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    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.

3 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        v-easonf-msft
        Community Support
        3 years ago

        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.