Modelling Dynamics 365 SystemUser hierarchy in PowerBI

The SystemUser entity in Dynamics 365 is a self referencing entity, that defines its hierarchy based on the parentSystemUserId field.

So how do you go about showing the hierarchy and utilizing it in report?

Here is one way that I found, that utilizes the DAX Path function.

1. Go to the data view and select the “SystemUser” table.
2. Add a new column and define your first field using the following function.
   ManagerHierarchy = Path(SystemUser[systemuserid],SystemUser[parentsystemuserid])
   
   This will add a column that lists all the managers of a systemuser, delimited by the pipe character “|”
3. Add another new column to show the depth of the current user
   HierarchyDepth = PATHLENGTH ( SystemUser[ManagerHierarchy] )
   
   This will add a column with integer values starting at 1 and going up to the max-depth of the record found in your system
4. Next, add columns, for each of the following functions (I went up to 5, as that was the max I had in my system)
   
   

   [ManagerFirstLevel] = LOOKUPVALUE (      SystemUser[fullname],      SystemUser[systemuserid],      PATHITEMREVERSE ( SystemUser[ManagerHierarchy], 2, TEXT ) )

   ManagerSecondLevel = if (SystemUser[HierarchyDepth] >= 3,      LOOKUPVALUE (      SystemUser[fullname],      SystemUser[systemuserid],      PATHITEMREVERSE ( SystemUser[ManagerHierarchy], 3, TEXT ) ), SystemUser[ManagerFirstLevel])

   ManagerThirdLevel = if (SystemUser[HierarchyDepth] >= 4,      LOOKUPVALUE (      SystemUser[fullname],      SystemUser[systemuserid],      PATHITEMREVERSE ( SystemUser[ManagerHierarchy], 4, TEXT ) ), SystemUser[ManagerSecondLevel])

   ManagerFourthLevel = if (SystemUser[HierarchyDepth] >= 5,      LOOKUPVALUE (      SystemUser[fullname],      SystemUser[systemuserid],      PATHITEMREVERSE ( SystemUser[ManagerHierarchy], 5, TEXT ) ), SystemUser[ManagerThirdLevel])

   
   Things to note:
   PathItem is used to get the value at a particular depth from the Path generated field (in this case SystemUser[ManagerHierarchy]). LookUpValue is then used to lookup that value’s fullname, so that it cant make for a pretty display value).
   In my case I had to use PathItemReverse, so that a depth of 2 would give me the first manager’s value and so on. If I just used PathItem, I would have had to reverse my depth values.
5. You now will have a SystemUser table with the names of each manager at the different levels. You could rename the columns, to have actual title names (Manager, Vice President, etc).
6. The final thing I did was to setup the hierarchy on the fields:
   
   By doing this, you can get drill downs working. The hierarchy settings shown above will start at the bottom most level (employee) and as you click the drill down button, it will move up the hierarchy. If instead you wanted it to work the other way, you can just reorder it in reverse.

Things to know:

1. This works only if you know the number of levels you are dealing with. So, the same concept would theoretically break on Accounts, which could be nested many levels deep.
2. This was modeled after this power-pivot sample: https://www.daxpatterns.com/parent-child-hierarchies/