Lookup Value in another table using a filter

[Decal]

This may be pretty basic for many of you, but here is what I am trying to do.  

I have a table of job requisitions and a table of job applicants.  I want to lookup a value in the applicant table ([new hire]) when the [candidate status] = "Ready for Hire".  The similar column between the two table is [job requisition]

I tried create a temptable:

VAR temptable = FILTER(RELATEDTABLE(dimApplicants),dimApplicants[candidate status]="Ready for Hire")

And then use a lookupvalue:

RETURN

LOOKUPVALUE (temptable[newhire],dimApplicants[job requistion],temptable[job requisition])

DAX doesn't like this.

I can always create a subset actual table and link to that, but I was just hoping to be more efficient.

[danextian]

Hi @Decal ,

LOOKUPVALUE does accept multiple lookup columns and values.

LOOKUPVALUE (
    dimApplicants[new hire],
    dimApplicants[job requistion], temptable[job requisition],
    dimApplicants[candidate status], "Ready for Hire"
)

[vicky_]

You can always try:

Measure = CALCULATE(MIN(dimApplicants[newhire]), FILTER(RELATEDTABLE(dimApplicants),dimApplicants[candidate status]="Ready for Hire"))

just note that this doesn't handle the case where there's 2+ candidates