Insights / RLS in Power BI / Part 12
Part 12 of 20 · Build itPower BI RLS manager hierarchy with PATH
The problem. "How do I make sure each manager sees only their own team, and everyone below them?" And when someone changes teams, their access should move with them.
The fix: dynamic RLS on your org chart. One report, one role, for 5 managers or 500.
- On the Employee table, add the chain of command for every row:
Path = PATH ( Employee[EmployeeID], Employee[ManagerID] )
- Add a team key: your own ID if anyone reports to you, otherwise your manager's ID.
- Create one role, Team Hierarchy, that keeps rows whose Path contains the signed-in user's team key:
PATHCONTAINS (
Employee[Path],
LOOKUPVALUE ( Employee[TeamKey], Employee[Email], USERPRINCIPALNAME () )
)
- Test with View as: tick Other user and enter the email, and tick the role.
- Move someone to a new team by changing one ManagerID. Their access follows.
- Publish, then add a security group to the role, not individual people.
Watch out:
- PATH functions don't work in role rules on DirectQuery. Build a flat manager-to-employee table in the source instead (one row per manager per person below them), with
[ManagerEmail] = USERPRINCIPALNAME(). That works in every storage mode. - On Direct Lake, build the Path column in the lakehouse, not as a calculated column.
Starter and finished Power BI projects for this chapter are on GitHub: github.com/Zohal987654321
Check before go-live
- Every manager was tested with View as (Other user + role)
- Moving one person to a new team changed their access as expected
- DirectQuery or Direct Lake models use a flat manager table, not PATH Desktop
Want a second pair of eyes on your own model? The RLS review checks your roles, relationships and workspace access against this checklist, at a fixed price.