Lookup with mutiple table - If else loop condition

[Kavithaa]

I have 2 tables as below. I have to create a new coloumn in "PO Table" - The value should be looked up from "Org Table".

Requester has to be looked wiht "Org Table - Engineer", then whatever the entries as not found - has to be looked up with "Org Table - Team Leader", 

then whatever the entries as not found - has to be looked up with "Org Table - Manager", 

then whatever the entries as not found - has to be looked up with "Org Table - Senior Manager", 

Can someone help me with the query.

PO Table:

Requester	PO Number	Cost	PO Date
John	123	€  100.00	03/05/2023
Cristy	234	€  200.00	23/04/2023
Tom	321	€  300.00	21/04/2023
Rocky	432	€  400.00	31/04/2023
Mitchel	111	€  150.00	01/05/2023
Kevin	211	€    50.00	01/05/2023

Org Table:

Engineer	Team Leader	Manager	Senior Manager
John	Cristy	Tom	Rocky
Michael	Cristy	Tom	Rocky
Angela	Mitchel	Tom	Rocky
Tony	Mitchel	Kevin	Rocky

[sevenhills · Accepted solution]

Not sure what is the output you are looking for ?

Check this sample, if this is what you are looking for ...

Source: PO Table and Org Tables:

Relationships:

Measure:

Requester Level = 
var _sel = SELECTEDVALUE('PO Table'[Requester])

var _LevelID = SWITCH( TRUE(), 
    -- is Engineer?
    LOOKUPVALUE('Org Table'[L4 ID], 'Org Table'[Engineer], _sel) <> BLANK(), LOOKUPVALUE('Org Table'[L4 ID], 'Org Table'[Engineer], _sel),
    -- is team leader
    LOOKUPVALUE('Org Table'[L3 ID], 'Org Table'[Team Leader], _sel) <> BLANK(), LOOKUPVALUE('Org Table'[L3 ID], 'Org Table'[Team Leader], _sel),
    -- is Manager
    LOOKUPVALUE('Org Table'[L2 ID], 'Org Table'[Manager], _sel) <> BLANK(), LOOKUPVALUE('Org Table'[L2 ID], 'Org Table'[Manager], _sel),
    -- is Senion Manager
    LOOKUPVALUE('Org Table'[L1 ID], 'Org Table'[Senior Manager], _sel) <> BLANK(), LOOKUPVALUE('Org Table'[L1 ID], 'Org Table'[Senior Manager], _sel),
    BLANK()
)

RETURN _LevelID

Final output: