how can i achieve this logic?

[Anonymous]

Hi all,

 i have a table like below image, how can i get final interview status field by using Interview round and Status field.

Data:

Candidate ID Interview Round Status Attitude Communication Business Technology

1	Round 1	Select	good	good	good	good
1	Round 2	Select	good	good	good	good
1	Round 3	Select	good	good	good	good
1	Round 4	Select	good	good	good	good
2	Round 1	Select	good	good	good	good
2	Round 2	Select	good	avg	avg	good
2	Round 3	Reject	avg	good	avg	good
3	Round 1	Select	good	avg	avg	good
3	Round 2	Hold	avg	good	avg	good
4	Round 1	Reject	avg	good	avg	good

[AlB · Accepted solution]

@Anonymous

I guess you should have posted the actual requirement to start with 

The alphabetical sorting approach does not work (by very little) with the new names so let's try this instead:

FinalInterviewStatus_V2 =
VAR _ConductedInterviews =
    CALCULATETABLE (
        VALUES ( Table1[Interview Round] ),
        ALLEXCEPT ( Table1, Table1[Candidate ID] )
    )
VAR _FinalRound =
    SWITCH (
        TRUE (),
        "Final" IN _ConductedInterviews, "Final",
        "HR" IN _ConductedInterviews, "HR",
        "Technical 2" IN _ConductedInterviews, "Technical 2",
        "Technical 1" IN _ConductedInterviews, "Technical 1"
    )
RETURN
    LOOKUPVALUE (
        Table1[Status],
        Table1[Candidate ID], Table1[Candidate ID],
        Table1[Interview Round], _FinalRound
    )