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 cat...
categorization
Dashboard
DAX
powerbi
recruiting
switch
Workday
  • dedelman_clng's avatar
    dedelman_clng
    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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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: