Forum Discussion
Need help on IF DAX query
I'm not sure if I need an IF query here or not, but I'm trying go up a management chain to find the manager of a user with 'VP' or 'Vice President' in their job title. All data is on the same table.
So I can do a lookupvalue, or calculate, and pull the title back for the manager based on unique fields. Every user has a unique UserName, and unique ID. So in the below table example, I can lookup a user's ManagerID and match to ID to get their title. All of that makes sense.
However, that manger may not have VP or Vice President in their title, and I would need to go up to the next manager of the current manager, just going up the chain until the title contains VP or Vice President. I'm open to any methods of doing this and am happy to provide more data if required. Appreciate all help in advance!
Table:
| UserName | ID | ManagerID | Title |
3 Replies
- JorgePinho
Solution Sage
The best approach is using parent-child hierarchy.
Check this resource and I believe you will understand how to do it: https://learn.microsoft.com/en-us/dax/understanding-functions-for-parent-child-hierarchies-in-dax
Hi rh128,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
I've managed to get everything on to a single table. Here is a bit better of a mock up with sample data and my terrible logic of how it could be done. Hopefully this clears the situation up. There is no set standard location of VP/Vice President in the chain either.
I did try using the child-parent hierarchy, and can see the lenght, but am unable to extract only the value that contains a specific title.UN EmpID SupervisorEmpId SupervisorUN JobTitle VP aa123 111 222 bb123 SPM dd123 bb123 222 333 cc123 LPM dd123 cc123 333 444 dd123 DIR dd123 dd123 444 555 ee123 Vice President dd123 ee123 555 Owner Starting with Row 1 (First user)
VP =
VAR CUN = aa123
VAR CJT = SPM
VAR NextLeader = bb123
VAR NextLeaderTitle = LPM
RETURN
IF(CONTAINSSTRING(CJT,"VP" OR "Vice President", ap[EmpID],
IF(CONTAINSSTRING(NextLeaderTItle,"VP" OR "Vice President"),NextLeader,
-- In this case, the "NextLeaderTitle" is LPM, so we would need to loop again and look at the next leader which is UN(User name) cc123 :
LOOKUPVALUE(ap[SupervisorUN],ap[UN],NextLeader)
Then we must get this leader title (Job Title for cc123). If it does NOT contain VP/Vice President, then we loop again using this UN and get the leader which is dd123.
Check to see if dd123's title contains VP/Vice President, which it does. So our output would be dd123. This is where the loop should complete.
