Forum Discussion
Lookup with mutiple table - If else loop condition
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 |
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 _LevelIDFinal output:
1 Reply
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 _LevelIDFinal output: