Cost per hire - in row with Candidate ID

[Anonymous]

Hello everyone, 

I've got stuck a little bit in a formula. 

For context, there are marketing campaigns for recruitment purposes setup to attract candidates to apply to certain vacancies (Vacancy_ID), some of them get hired from that specific campaign and therefore we want to measure the "Cost per hire" based on that campaign expenses. 

I have 2 tables connected via "Vacancy ID", 1st is 'report' the 2nd one is "costperhire" (one from the screenshot below), I used the formula below to get how many candidates were hired from that specific campaign.

Hired = CALCULATE(DISTINCTCOUNT('report'[Candidate_CandidateGUId]), FILTER('report', 'costperhire'[channel] = 'report'[Centralized Sources Detail] && 'costperhire'[start] <= 'report'[Candidate_SubmittedDate] && 'costperhire'[end] >= 'report'[Candidate_SubmittedDate] && 'costperhire'[vacancy_ID] = 'report'[Vacancy_VacancyId] && 'report'[Candidate_Hired1] = 1 && 'report'[Candidate_CandidateGUId] = 'report'[Candidate_CandidateGUId]))

 

What I'm trying to achieve is to place the "Cost per hire" price on the same row as the "Candidate_CandidateGUId" in the 'report' table.

'report' table contains:

Candidate_CandidateGUId | Candidate_SubmittedDate | Vacancy_VacancyId (vacancy_ID) | Centralized Sources Detail (channel) | Candidate_Hired1 (1 or 0)

I want to add another column with "Cost per hire"

It seems to be a "LookUpValue" function, but I'm having a hard time getting it right. 

Can you please help me with the formula here? 

Please let me know if I should provide more information. 

Thank you very much!

[Anonymous · Accepted solution]

Found the solution eventually, it was the RELATED function that needed to be used in order to work. Thank you very much! 

Cost per Hire = IF(RELATED(costperhire[vacancy_ID]) = 'report'[Vacancy_VacancyId] && RELATED(costperhire[start]) <= 'report'[Candidate_SubmittedDate] && RELATED(costperhire[end]) >= 'report'[Candidate_SubmittedDate] && RELATED(costperhire[channel]) = 'report'[Centralized Sources Detail] && 'report'[Candidate_Hired1] = 1, RELATED(costperhire[Cost per hire]))