Lookup selected cell value from a range of columns and return a result

[RobWayne]

Hello to all the community,

I am trying to use DAX to return a result between two tables not directly connected between each other.

Table 1

Name	Job	Level
Alan	Accountant	I
Mark	Accountant	II
John	Manager	III

Job List

Job	I	II	III
Accountant	B	C	D
Manager	D	E	F

By taking the parameters Job + Level, I would like to return the corresponding value for each name based on level

Table 1

Name	Job	Level	Value
Alan	Accountant	I	B
Mark	Accountant	II	C

Current solution that I have found is to do a lot of nested lookupvalues 

IF(Level="I",LOOKUPVALUE('JOB LIST'[I],'JOB LIST'[JOB],Job),IF(Level="II",LOOKUPVALUE('JOB LIST'[II],'JOB LIST'[JOB],Job),IF(Level="III",LOOKUPVALUE('JOB LIST'[III],'JOB LIST'[JOB],Job),IF(Level="IV",LOOKUPVALUE('JOB LIST'[IV],'JOB LIST'[JOB],Job))))),LOOKUPVALUE('JOB LIST'[No Level],'JOB LIST'[JOB],Job)).

I would like to learn a more optimised way to do it,

I have tried by unpivoting of the columns but I am not sure how to write a condition to similar to IF ( Table1 [Job] + Table1 [Level] = Job List [Job] + Unpivot Job List [Level] )

Return Value

your clairvoyance and expertise is much appreciated

[dedelman_clng]

Hi @RobWayne -

Unpivoting Job List is the first step. You want the data to look like this:

Job	Level	Value
Accountant	I	B
Accountant	II	C
Accountant	III	D
Manager	I	D
Manager	II	E
Manager	III	F

Then the DAX should be straightforward

JobValue =
LOOKUPVALUE (
    JobList[Value],
    JobList[Title], Table1[Title],
    JobList[Level], Table1[Level]
)

Hope this helps

David