Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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@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] )
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 )
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 |
@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
)
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 |
@Anonymous
I think @Zubair_Muhammad's solution, which is very interesting, should work in that case too. Have you tried it?
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] )
@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] )
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |