Retrieve count of candidates based on criteria.

[dataprofile]

Hi! 

I have a table(candidates_table) with candidate id, application id and candidate stage. The table looks as follows: 

candidate_id      | application_id      | Candidate_stage

1                          CAND-1                  Interview

1                          CAND-1                  Offer

1                          CAND-1                  Ready for Hire

2                          CAND-2                  Interview

2                          CAND-2                  Offer

I'm trying to create dax code to retrieve the distinct count of candidates that made it to the Candidate Stage "Offer" but didn't make it to the "Ready for Hire" stage (in this case the count would be 1=candidate 2). Any ideas would be appreciated! Thanks!

[MahyarTF]

Hi,

You could create the 2 separate tables for the Offer and Ready for Hire separately :

1- 

OfferTable = CALCULATETABLE(

                            SELECTCOLUMNS(sheet228,

                                          "Candidate_Id", Sheet228[candidate_id],

                                          "Application_Id", Sheet228[application_id]),

                           Sheet228[Candidate_stage] = "Offer")

2- 

ReadyforHireTable = CALCULATETABLE(

                            SELECTCOLUMNS(sheet228,

                                          "Candidate_Id", Sheet228[candidate_id],

                                          "Application_Id", Sheet228[application_id]),

                           Sheet228[Candidate_stage] = "Ready for Hire")

And then create a measure in your main table as below : 

CandOffer =

Var result = COUNTROWS(OfferTable) - COUNTROWS(ReadyforHireTable)

return result

- Now you have the Candidate separately and the count you mentioned in your post.

Thanks for your Kudos

Please mark it as solution if it helps you

[CNENFRNL · Accepted solution]

For fun only, a showcase of powerful Excel worksheet formula; btw, I'm just got hired with decent salary by a big company for my proficiency in Excel.🤣