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
    )

[AlB]

Hi @Anonymous

Try this for your calculated column. It should work as long as there are no more than 9 rounds (since "Round 10" will show up before "Round 2" when sorting alphabetically to look for the last round)

FinalInterviewStatus =
VAR _FinalRound =
    CALCULATE (
        MAX ( Table1[Interview Round] ),
        ALLEXCEPT ( Table1, Table1[Candidate ID] )
    )
RETURN
    LOOKUPVALUE (
        Table1[Status],
        Table1[Candidate ID], Table1[Candidate ID],
        Table1[Interview Round], _FinalRound
    )

Code formatted with  

[Anonymous]

Thanks @AlB

My actual requirement:  interview round has Technical 1 --> Technical 2 --> HR --> Final

how can I achieve? 

Candidate ID Interview Round Status Attitude Communication Business Technology

1	Technical 1	Select	good	good	good	good
1	Technical 2	Select	good	good	good	good
1	HR	Select	good	good	good	good
1	Final	Select	good	good	good	good
2	Technical 1	Select	good	good	good	good
2	Technical 2	Select	good	avg	avg	good
2	HR	Reject	avg	good	avg	good
3	Technical 1	Select	good	avg	avg	good
3	Technical 2	Hold	avg	good	avg	good
4	Technical 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
    )

[Anonymous]

Hi @AlB

Now I'm  facing New Problem 

some candidate attend interview more than one time, so I want to select status based on the maximum date of Interview Date 

Ex: Candidate ID = 5 attend  2 times for Technical 2

 I hope this is the last question to you... Thank you in advance 

Interview Date Candidate ID Interview Round Status Attitude Communication Business Technology Final Interview Status

1/24/18 3:00 PM	1	Technical 1	Select	good	good	good	good	Select
1/24/18 3:00 PM	1	Technical 2	Select	good	good	good	good	Select
1/24/18 3:00 PM	1	HR	Select	good	good	good	good	Select
1/24/18 3:00 PM	1	Final	Select	good	good	good	good	Select
1/24/18 3:00 PM	2	Technical 1	Select	good	good	good	good	Reject
1/24/18 3:00 PM	2	Technical 2	Select	good	avg	avg	good	Reject
1/24/18 3:00 PM	2	HR	Reject	avg	good	avg	good	Reject
1/24/18 3:00 PM	3	Technical 1	Select	good	avg	avg	good	Hold
1/24/18 3:00 PM	3	Technical 2	Hold	avg	good	avg	good	Hold
1/24/18 3:00 PM	4	Technical 1	Reject	avg	good	avg	good	Reject
1/24/18 3:00 PM	5	Technical 1	Select	good	good	good	good	Reject
1/24/18 3:00 PM	5	Technical 2	Hold	good	good	good	good	Reject
1/24/18 4:00 PM	5	Technical 2	Reject	avg	good	avg	good	Reject

[AlB]

@Anonymous

I think @Zubair_Muhammad's solution, which is very interesting, should work in that case too. Have you tried it? 

[AlB]

Hi  @Zubair_Muhammad

Very cool, interesting approach. May I ask the reason for the  & ""  in the last argument of SELECTCOLUMNS( ), i.e.  in

    [Status] & ""

Thanks very much

[Zubair_Muhammad · Accepted solution]

@Anonymous@AlB

With the dates in place, the calculation should become simple

Final Status =
MAXX (
    TOPN (
        1,
        FILTER ( Table1, [Candidate ID] = EARLIER ( [Candidate ID] ) ),
        [Interview Date], DESC
    ),
    [Status]
)

[Zubair_Muhammad]

@AlB @Anonymous

I noticed there was a flaw in my original formula although it worked with sample data

& "" is used to break data lineage as explained in this article

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

Better to have used this one. Basically we assign priority to each interview stage and then fetch the status for the latest stage of interview for each candidate

Final Interview Status =
VAR Possible_Status = { ( "Technical 1", 1 ),( "Technical 2", 2 ),( "HR", 3 ),( "Final", 4 ) }
VAR All_Status_Individual =
    SELECTCOLUMNS (
        CALCULATETABLE ( Table1, ALLEXCEPT ( Table1, Table1[Candidate ID] ) ),
        "Value1", [Interview Round] & "",
        "Status", [Status]
    )
VAR CombinedTable =
    NATURALINNERJOIN ( All_Status_Individual, Possible_Status )
RETURN
    MAXX ( TOPN ( 1, CombinedTable, [Value2], DESC ), [Status] )

[AlB]

@Zubair_Muhammad

I see. I'll take a look at the article. Thanks very much

What was the issue with the original formula?

[Zubair_Muhammad]

@Anonymous

One possible way.. to use this calculated column

Final Interview Status =
VAR Possible_Status = { ( "Select", 1 ),( "Hold", 2 ),( "Reject", 3 ) }
VAR All_Status_Individual =
    SELECTCOLUMNS (
        CALCULATETABLE (
            VALUES ( Table1[Status] ),
            ALLEXCEPT ( Table1, Table1[Candidate ID] )
        ),
        "Value1", [Status] & ""
    )
VAR CombinedTable =
    NATURALINNERJOIN ( Possible_Status, All_Status_Individual )
RETURN
    MAXX ( TOPN ( 1, CombinedTable, [Value2], DESC ), [Value1] )