Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Using SWITCH to put candidates into categories, however ...

 ... individual instances will usually belong to multiple categories.

Question
Is there a way to have a singular column/measure which permits candidates to simultaneously be in multiple defined categories at once, based on their stage progression?

Context
I am building a dashboard for our recuitment team based off of extract from Workday Recruiting. There are six applicant/candidate stages we monitor:
1. Review
2. Screen

3. Interview

4. Reference Check

5. Offer

6. Ready for Hire

I am looking to report on the "Candidate Progression Funnel" to monitor progression at each of these six steps. This means that each candidate should count towards not only for their current stage (e.g. Interview) but also the preceding stages they've come through (Review & Screen). An individual who makes it to an Interview also needs to be in the counts for Review and Screen. Someone who only makes it to Review only counts towards Review while someone who makes it to Offer stage counts for stages 1-5, but not Ready for Hire yet.

I already have created six individual calculated columns for each for the Progression Stages:

 

Candidate Progress 03: Interview = 
   IF(
        (EXTRACT_02[Candidate Stage]="Interview") || 
        (EXTRACT_02[Candidate Stage]="Reference Check") ||
        (EXTRACT_02[Candidate Stage]="Offer") || 
        (EXTRACT_02[Candidate Stage]="Ready for Hire") || 

        (EXTRACT_02[Last Recruiting Stage]="Interview") ||
        (EXTRACT_02[Last Recruiting Stage]="Reference Check") ||
        (EXTRACT_02[Last Recruiting Stage]="Offer") ||
        (EXTRACT_02[Last Recruiting Stage]="Ready for Hire") ||,
("Interview"),
("No")
   )

 

The issue is now that I have six different calculated columns rather than a singular one for the purposes of visualization. I'm in need of a single column which has all six progession stages in the same place.

I've attempted to use SWITCH to no avail, yet:

 

Candidate Progress: All 01 = 

SWITCH(

TRUE(),

EXTRACT_02[Last Recruiting Stage]="Review" ||
EXTRACT_02[Last Recruiting Stage]="Screen" ||
EXTRACT_02[Last Recruiting Stage]="Interview" ||
EXTRACT_02[Last Recruiting Stage]="Reference Check" ||
EXTRACT_02[Last Recruiting Stage]="Offer" ||
EXTRACT_02[Last Recruiting Stage]="Ready for Hire",
"Review",

EXTRACT_02[Last Recruiting Stage]="Screen" ||
EXTRACT_02[Last Recruiting Stage]="Interview" ||
EXTRACT_02[Last Recruiting Stage]="Reference Check" ||
EXTRACT_02[Last Recruiting Stage]="Offer" ||
EXTRACT_02[Last Recruiting Stage]="Ready for Hire",
"Screen",

EXTRACT_02[Last Recruiting Stage]="Interview" ||
EXTRACT_02[Last Recruiting Stage]="Reference Check" ||
EXTRACT_02[Last Recruiting Stage]="Offer" ||
EXTRACT_02[Last Recruiting Stage]="Ready for Hire",
"Interview",

EXTRACT_02[Last Recruiting Stage]="Reference Check" ||
EXTRACT_02[Last Recruiting Stage]="Offer" ||
EXTRACT_02[Last Recruiting Stage]="Ready for Hire",
"Reference Check",

EXTRACT_02[Last Recruiting Stage]="Offer" ||
EXTRACT_02[Last Recruiting Stage]="Ready for Hire",
"Offer",

EXTRACT_02[Last Recruiting Stage]="Ready for Hire",
"Ready for Hire",

"NA"
)

 

I believe this is happening b/c "Review" encompasses all applicants (rightfully so) based on the logic and thus applicants aren't counting towards multiple stage categories.

How might I have a singular column/measure which permits candidates to simultaneously be in multiple defined categories at once based on their stage progression?

categorization
Dashboard
DAX
powerbi
recruiting
switch
Workday

9 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous -

     

    Create a dimension table for your statuses like this

     

    Idx Status
    1 Review
    2 Screen
    3 Interview
    4 Reference Check
    5 Offer
    6 Ready for Hire

     

    For the column "Status" do "Sort By Column" on Idx

     

    In your model, link Status with whichever Status field on the candidate record you want to key off of. This should be 1-to-Many (Status to Candidate)

     

    Then your measure will be

     

    Total Candidates =
CALCULATE (
    COUNTA ( Candidates[Candidate] ),
    FILTER ( ALL ( Statuses ), Statuses[Idx] <= MAX ( Statuses[Idx] ) )
)

    Hope this helps

    David

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you David this is wonderful help. I have a clarifying point. Based on the Candidate's A thru H included as examples, according to my needs the counts shoudl be:

      Review: 8

      Screen: 6

      Interview: 4

      Reference: 3

      Offer: 2

      Ready for Hire: 1

       

      I likely miscommunicated about the candidate stage progression. "Ready for Hire" is the last stage and thus b/c we only have one candidate at that stage there is only one count there. For "Offer", as the penultimate stage, the count is 2 (one for the candidate at "Offer" and then another count for the candidate just mentioned who progressed to the final stage "Ready for Hire".)

      Based on this, how might I shift the needed measure to count accurately? Again, thank you.

       

      • dedelman_clng's avatar
        dedelman_clng
        Icon for Community Champion rankCommunity Champion
        5 years ago

        My apologies - I did it backwards.  We just need to change the MAX to MIN and <= to >=

         

        Total Candidates =
CALCULATE (
    COUNTA ( Candidates[Candidate] ),
    FILTER ( ALL ( Statuses ), Statuses[Idx] >= MIN ( Statuses[Idx] ) )
)

         

        David