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.

Choosing an org chart visual in Power BI

Once each row carries its complete reporting chain as separate columns, the visual is the easy part — every option expects roughly the same input: a flat table where each row holds its position in the hierarchy as an ordered set of columns. Your choices, from native to custom:

• Matrix — drop Level0 through LevelN into the rows for an expandable, indented hierarchy. Native, zero install, good for reporting.
• Decomposition tree — the closest native visual to an interactive org chart; users drill from the top down, branch by branch.
• Hierarchy slicer — a native-feeling slicer (from AppSource) for filtering the rest of a report by reporting line.
• AppSource org chart visuals — a true boxes-and-lines chart. The Org Chart and Visio visuals render directly from the level-per-column shape, several of them free.
• Custom HTML/SVG visual — full control over layout when none of the above fits.

Building a dynamic org chart

"Dynamic" usually means two things: the chart is interactive, and it stays correct as the organisation changes. The PATH pattern gives you both for free.

• Interactive drill-down — feed the level columns into a decomposition tree or a drillable matrix and users can expand and collapse any reporting line on the page.
• Self-maintaining — because the hierarchy is derived from the manager column, you never edit the chart. When someone changes manager, you update one Lvl1 value and PATH / PATHITEM recompute the whole structure on next refresh.

That second point is the real payoff: a hand-drawn org chart is stale the day someone moves teams; a PATH-driven one is never out of date.

Adding employee photos to the org chart

To show photos against each person — the common "org chart with pictures" request — add a column holding each employee's image URL and set its Data category to Image URL in the column tools ribbon. Org-chart visuals that support images, and custom HTML/SVG visuals, will then render the photo on each node alongside name and title.

The photo URLs can come from your HR system, SharePoint, or Microsoft Entra ID user profiles — wherever the organisation already stores them.

Sourcing the hierarchy from Active Directory

You may not need an HR export at all. Microsoft Entra ID (formerly Azure Active Directory) already stores each user's manager attribute — the exact self-referencing relationship this pattern needs. Pull users and their manager into Power BI via:

• Microsoft Graph — query /users with the manager relationship expanded
• The Microsoft Entra ID / Azure AD connector in Power BI Desktop
• A scheduled export to a table your model already reads

Once the user and manager IDs are in a table, apply the same PATH and PATHITEM columns covered above — the rest of the pattern is identical whether the source is SAP SuccessFactors, Workday, or Active Directory.

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

Frequently asked questions

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.