Create Organizational Charts in Power BI

The challenge — recursive employee–manager relationships

One of the recurring problems Power BI developers run into is how to handle employee–manager relationships. Most HR systems store all employees in a single table, and some of those employees are managers to other employees in the same table. That's a recursive (or self-referencing) relationship.

The rule is simple: one employee has one manager, but one manager can have many direct reports. Easy to draw on a whiteboard, less obvious how to make Power BI render it as a hierarchy you can slice and visualise.

The data model

You typically have two tables. The first is the Employee table — a flat list of every person and their ID:

Employee
─────────────────
Employee ID │ Name
─────────────────
     1      │ Jane
     2      │ John
     3      │ Sara
     6      │ Tom
     7      │ Anna
     8      │ Mark
     9      │ Lisa
    15      │ Paul
    16      │ Emma
    17      │ David
    18      │ Maya

The second table — call it EmpManager — assigns each employee an immediate parent (their manager's Employee ID). For convenience here we'll call that column Lvl1:

EmpManager
──────────────────────────
Employee ID │ Name  │ Lvl1
──────────────────────────
     2      │ John  │  1
     6      │ Tom   │  2
     7      │ Anna  │  2
     8      │ Mark  │  2
     9      │ Lisa  │  2
    15      │ Paul  │  6
    16      │ Emma  │  6
    17      │ David │  6
    18      │ Maya  │  6

Reading those rows: employee 2 (John) supervises 6, 7, 8, 9. Employee 6 (Tom) in turn supervises 15, 16, 17, 18. Multiple levels of management, all encoded with a single column referencing IDs in the same table.

Building the path with PATH()

DAX has a function built specifically for this: PATH. It walks up the recursive relationship from any row to the top of the chain, returning a delimited string of every ID along the way.

Add a calculated column to EmpManager:

EntityPath = PATH ( EmpManager[Employee ID], EmpManager[Lvl1] )

The first argument is the column with the row's own ID. The second is the column with the parent ID. The result for our sample data:

Employee ID │ Name  │ Lvl1 │ EntityPath
───────────────────────────────────────
     2      │ John  │  1   │ 1|2
     6      │ Tom   │  2   │ 1|2|6
     7      │ Anna  │  2   │ 1|2|7
     8      │ Mark  │  2   │ 1|2|8
     9      │ Lisa  │  2   │ 1|2|9
    15      │ Paul  │  6   │ 1|2|6|15
    16      │ Emma  │  6   │ 1|2|6|16
    17      │ David │  6   │ 1|2|6|17
    18      │ Maya  │  6   │ 1|2|6|18

Each row now contains the full reporting chain from that person all the way up to the top.

Pulling level names with PATHITEM

The path is in IDs, but reports usually need names. PATHITEM picks out the ID at a given position in the chain, and LOOKUPVALUE resolves it to a name. Together they let you build one calculated column per level of the hierarchy.

For the top of the chain (level 0):

Level0 =
VAR LevelNumber = 1
VAR LevelKey = PATHITEM ( EmpManager[EntityPath], LevelNumber, INTEGER )
VAR LevelName = LOOKUPVALUE (
    EmpManager[Name],
    EmpManager[Employee ID], LevelKey
)
RETURN LevelName

To create level 1, level 2 and so on, copy the column and change LevelNumber to 2, 3, 4… You end up with one column per organisational tier:

Employee ID │ Name  │ Level0 │ Level1 │ Level2 │ Level3
────────────────────────────────────────────────────────
    15      │ Paul  │ Jane   │ John   │ Tom    │ Paul
    16      │ Emma  │ Jane   │ John   │ Tom    │ Emma
    17      │ David │ Jane   │ John   │ Tom    │ David
    18      │ Maya  │ Jane   │ John   │ Tom    │ Maya

HR conventions usually call the top of the org "level 0" — the CEO, founder, or whatever sits at the apex. Adjust the LevelNumber variable accordingly so your indexing matches your organisation.

Visualising the org chart

Once each row carries its complete reporting chain as separate columns, you have several options for putting it on a page:

• Drop Level0 through LevelN into a hierarchy slicer, decomposition tree, or matrix visual
• Use a third-party AppSource visual designed for org charts (several free ones render directly from this column shape)
• Feed the levels into a custom visual built with HTML/SVG if you need full control over layout

The data work is the hard part. Every visual on AppSource expects roughly the same input — a flat table where each row contains its position in the hierarchy as an ordered set of columns. PATH and PATHITEM get you there.

When this pattern earns its keep

Beyond visualising the chart itself, the same calculated columns unlock a few common reporting needs:

• Roll-up KPIs by reporting line — headcount, salary cost, average tenure aggregated up any tier
• Row-level security tied to manager chains — a manager sees only their own reports, recursively, by filtering on the path string
• Org changes that don't break the model — when reporting lines move, only the Lvl1 column updates; PATH and PATHITEM recompute automatically

---

This pattern is one of those small DAX tricks that pays itself back many times over once you've used it once. If your HR data lives in a single self-referencing table, you can move from "we don't have an org chart in Power BI" to a working hierarchy in an afternoon.