Forum Discussion

Decal's avatar
Decal
Helper I
2 years ago

Lookup Value in another table using a filter

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.
Need Help

2 Replies

  • vicky_'s avatar
    vicky_
    Super User
    2 years ago

    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

  • danextian's avatar
    danextian
    Super User
    2 years ago

    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"
)