When working with big data, you will sometimes be in situations where you need to harness the capabilities of business intelligence but are inhibited by a lack of automatic hierarchy detection. Currently the relationship manager in Power BI does not directly support parent-child hierarchies for self-referencing tables. Because of this, you may often find yourself wondering how to get the system to recognize your desired relationship.

Luckily the formula language within Power BI, DAX, does have functions that allow the user to develop self-referencing columns within tables, which in turn allows for the creation of internal hierarchies. In today’s blog, we will consider how to use DAX to flesh out and visualize this difficult hierarchy information. We will discuss various strategies and their limitations.

Challenge:

Opportunity management is a typical subject that organizations would like to better understand with business intelligence. When data is queried and brought into Power BI, Relationships with records – such as Parent Accounts and Child Contacts – are typically understood and automatically set. In this case, we would like to consider the “Owner” field on Opportunity records in Dynamics. This field stems from a one-to-many relationship between the Systemuser entity and the Opportunity entity.

Consider a Sales Executive who would like to filter their report based on the Manager of Opportunity Owners. Additionally, the Executive would like to go up another level in the hierarchy (and filter on the Manager’s Manager). There are two ways that we can resolve this.

Solution 1: Lookups

The first is the use of only Lookup columns.

We will create our lookup columns within the Systemuser’s table. When pulling data from Dynamics 365, the Systemuser entity contains columns for systemuserid and _parentsystemuserid_value. Using these fields, we can create a self-referential lookup in DAX that will allow us to display the manager’s fullname.

• Manager FullName = LOOKUPVALUE(systemusers[fullname],systemusers[systemuserid], systemusers[_parentsystemuserid_value])

Once we have created the Manager fullname column, we can then create the column to display the manager’s manager.

• With this DAX: Manager's Manager FullName = LOOKUPVALUE(systemusers[FullName], systemusers[systemuserid],LOOKUPVALUE(systemusers[_parentsystemuserid_value],systemusers[fullname], systemusers[Manager FullName]))

In this way we can successfully display multiple levels of an internal hierarchy in Power BI. But using a Lookup within a Lookup like this requires a good deal of processing power. It can slow down the system and increase wait times. Therefore, it should be used sparingly.

Solution 2: Index Column and Path Function

The second method involves the creation of an index column and the use of the path function.

We set an index column in the systemusers dataset in Query Editor.

And we rename the column.

From here we can create the calculated columns in the sysemusers table.

Using the following DAX:

MgrPathIndex = LOOKUPVALUE( systemusers[UserPathIndex], systemusers[systemuserid], systemusers[ManagerID] )

We create a numeric index column for users in this step for managers with the [systemuserid] and the [ManagerId]. These id fields are text GUIDs in CRM, so we need to convert them to numeric ID columns in order to use the PATH function in DAX.

Using this equation in DAX:

UserPath = PATH( systemusers[UserPathIndex], systemusers[MgrPathIndex] )

The PATH function is returning the user Index as well as the user’s managers indexes from the highest point of the hierarchy. If you are in the 3^rd position of the hierarchy, you will receive a value in the column as “0 | 1 | 2” where 0 is level 1 ( the highest), 1 is the 2^nd level, and 2 is the 3^rd level. This method puts less stress on the system and is easier to use with a large hierarchy. It is the most efficient method and should be considered a best practice.

There is indeed a workaround or a solution for everything in Dynamics 365 and Microsoft Business Applications. Be sure to subscribe to our blog for more tips and tricks!

Happy Dynamics 365’ing