Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

if statement lookup

i need a formula like this 

if an employee has in column Action ONLY "plan"= 1 otherwise 2.  see picture below.

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous 

    You could also use this formula

    Result Column = 
IF (
    'Table'[Action] = "Plan"
        && CALCULATE (
            COUNTA ( 'Table'[Action] ),
            FILTER ( 'Table', 'Table'[Name employee] = EARLIER ( 'Table'[Name employee] ) )
        ) = 1,
    1,
    2
)

    Result:

     

    Best Regards,

    Lin

2 Replies

  • tex628's avatar
    tex628
    Community Champion
    7 years ago
    IF(
CALCULATE(
SELECTEDVALUE([Action]) , 
ALL('Table'),
[Employee name] = EARLIER([Employee name])
) = "Plan" , 1 , 0 )
  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Anonymous 

    You could also use this formula

    Result Column = 
IF (
    'Table'[Action] = "Plan"
        && CALCULATE (
            COUNTA ( 'Table'[Action] ),
            FILTER ( 'Table', 'Table'[Name employee] = EARLIER ( 'Table'[Name employee] ) )
        ) = 1,
    1,
    2
)

    Result:

     

    Best Regards,

    Lin