Power BI Hierarchy Slicer Display Employee Hierarchy Data

Good evening fellow developers,

Here's how to display an employee hierarchy (employees and their supervisor/manager) using the Power BI Hierarchy Slicer custom visual. For this tutorial, I'm using the ContosoRetailDW database which can be downloaded from Microsoft's SQL Server database examples. This SQL Server database already has an Employee table of which an employee is assigned with a parentkey attribute. Next is to create a view that will be used as the Power BI's dataset. This view will has the employee name, employee id, manager name and manager id columns used in generating hierarchical levels in Power BI.

Use ContosoRetailDW
Go
Create View vwEmployeeManager
As
SELECT 
      [EmployeeKey] as [Employee ID]
      ,([FirstName] + ' ' + IsNull([MiddleName], '') + ' ' + [LastName]) as [EmployeeName]
      ,IsNull([ParentEmployeeKey], 18) As [ManagerID]
	  ,IsNull((
				Select	[FirstName] + ' ' + IsNull([MiddleName], '') + ' ' + [LastName]
				From [ContosoRetailDW].[dbo].[DimEmployee] P Where P.EmployeeKey = E.ParentEmployeeKey
	   ), [FirstName] + ' ' + IsNull([MiddleName], '') + ' ' + [LastName]) as [Manager Name]
      ,[Title]
  FROM [ContosoRetailDW].[dbo].[DimEmployee] E

Then create a Power BI Desktop file and load the data from the created view. Next is you need to add calculated columns that will define the parent-child hierarchy. You may set the appropriate levels based from your current organization setup.

HierarchyPath = PATH(vwEmployeeManager[Employee ID], vwEmployeeManager[ManagerID])

HierarchyDepth = PATHLENGTH (vwEmployeeManager[HierarchyPath])

Level1 = 
LOOKUPVALUE (
    vwEmployeeManager[EmployeeName], 
    vwEmployeeManager[Employee ID],
    PATHITEM ('vwEmployeeManager'[HierarchyPath], 1, INTEGER )
)

Level2 = 
IF(
    vwEmployeeManager[HierarchyDepth] >= 2, 
    LOOKUPVALUE (
        vwEmployeeManager[EmployeeName], 
        vwEmployeeManager[Employee ID],
        PATHITEM (vwEmployeeManager[HierarchyPath], 2, INTEGER )
    ),
    vwEmployeeManager[Level1]
)

Level3 = 
IF(
    vwEmployeeManager[HierarchyDepth] >= 3, 
    LOOKUPVALUE (
        vwEmployeeManager[EmployeeName], 
        vwEmployeeManager[Employee ID],
        PATHITEM (vwEmployeeManager[HierarchyPath], 3, INTEGER )
    ),
    vwEmployeeManager[Level2]
)

Level4 = 
IF(
    vwEmployeeManager[HierarchyDepth] >= 4, 
    LOOKUPVALUE (
        vwEmployeeManager[EmployeeName], 
        vwEmployeeManager[Employee ID],
        PATHITEM (vwEmployeeManager[HierarchyPath], 4, INTEGER )
    ),
    vwEmployeeManager[Level3]
)

Level5 = 
IF(
    vwEmployeeManager[HierarchyDepth] >= 5, 
    LOOKUPVALUE (
        vwEmployeeManager[EmployeeName], 
        vwEmployeeManager[Employee ID],
        PATHITEM (vwEmployeeManager[HierarchyPath], 5, INTEGER )
    ),
    vwEmployeeManager[Level4]
)

Lastly, drag these calculated columns to the Hierarchy Slicer fields pane. Once done, the Hierarchy Slicer will be populated with data as shown below.

See reference for the parent-child hierarchy calculated columns here..