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

[Anonymous]

 ... 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?

[dedelman_clng]

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]

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 · Accepted solution]

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

[Anonymous]

Great, thank you for the quick reply. I am close but for some reason still off. I am getting the straightforward count at each stage, rather than the cumulative progressive count.

And I cannot get this thing to sort for some reason despite having done this:

and this:

[Anonymous]

I believe I have the DIM table and connection setup properly:

[dedelman_clng]

Make sure on the visual that you are using the correct status column and correct measure. It looks like you're just using a count instead of the measure.

Otherwise, can you share a copy of your pbix with any sensitive data removed? While the screen shots are sometimes helpful, quite often they are lacking a context that can only be seen in the entire report (other model tables interfering with filters, wrong columns being used in a visual, etc).

Dropbox, google drive, OneDrive, github, etc are good ways to share the report file.

[Anonymous]

hey there! is there any world I might grab a brief Zoom with you to hammer out a remaining issue I'm finding? and I am NOT above bribery with a Starbucks (or wherever) gift card as a token of appreciation.

[Anonymous]

Thank you, I think I figured it out (I was creating a column instead of measure) though I still can't find why the Sort By aspect isn't working. Luckily, by nature, the data is ascending so I can sort that way. Thank you very much.

[Anonymous]

Last note here: @dedelman_clng is a super kind human and found time to help hammer out the remaining issue which was related to the "Key Column" configuration in the Model view. This needed to be set to "Select a column with unique values" which immediately fixed the underlying issue.