Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

how can i achieve this logic?

Hi all,

 

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

 

Input.png

 

Data:

 

Candidate ID Interview Round Status Attitude Communication Business Technology

1Round 1Selectgoodgoodgoodgood
1Round 2Selectgoodgoodgoodgood
1Round 3Selectgoodgoodgoodgood
1Round 4Selectgoodgoodgoodgood
2Round 1Selectgoodgoodgoodgood
2Round 2Selectgoodavgavggood
2Round 3Rejectavggoodavggood
3Round 1Selectgoodavgavggood
3Round 2Holdavggoodavggood
4Round 1Rejectavggoodavggood
2 ACCEPTED SOLUTIONS

@Anonymous

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

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
    )

 

 

 

View solution in original post

@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]
)

Regards
Zubair

Please try my custom visuals

View solution in original post

10 REPLIES 10
AlB
Super User
Super User

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   www.daxformatter.com

Anonymous
Not applicable

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

1Technical 1Selectgoodgoodgoodgood
1Technical 2Selectgoodgoodgoodgood
1HRSelectgoodgoodgoodgood
1FinalSelectgoodgoodgoodgood
2Technical 1Selectgoodgoodgoodgood
2Technical 2Selectgoodavgavggood
2HRRejectavggoodavggood
3Technical 1Selectgoodavgavggood
3Technical 2Holdavggoodavggood
4Technical 1Rejectavggoodavggood

@Anonymous

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

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
Not applicable

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 PM1Technical 1SelectgoodgoodgoodgoodSelect
1/24/18 3:00 PM1Technical 2SelectgoodgoodgoodgoodSelect
1/24/18 3:00 PM1HRSelectgoodgoodgoodgoodSelect
1/24/18 3:00 PM1FinalSelectgoodgoodgoodgoodSelect
1/24/18 3:00 PM2Technical 1SelectgoodgoodgoodgoodReject
1/24/18 3:00 PM2Technical 2SelectgoodavgavggoodReject
1/24/18 3:00 PM2HRRejectavggoodavggoodReject
1/24/18 3:00 PM3Technical 1SelectgoodavgavggoodHold
1/24/18 3:00 PM3Technical 2HoldavggoodavggoodHold
1/24/18 3:00 PM4Technical 1RejectavggoodavggoodReject
1/24/18 3:00 PM5Technical 1SelectgoodgoodgoodgoodReject
1/24/18 3:00 PM5Technical 2HoldgoodgoodgoodgoodReject
1/24/18 4:00 PM5Technical 2RejectavggoodavggoodReject

@Anonymous

 

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

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

 

 

@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]
)

Regards
Zubair

Please try my custom visuals

@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] )

 


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

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

 

What was the issue with the original formula?

@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] )

how can i achieve this logic.png 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.