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

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity 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