How do I implement Dynamic Row Level Security (RLS) in Power BI using an Organizational Hierarchy?

All answers to this question.

Dynamic RLS is the way to go. You’ll want to create a calculated column using PATH(User[EmployeeKey], User[ManagerKey]) to flatten the hierarchy. Then, in your RLS role, use a filter like PATHCONTAINS(User[PathColumn], LOOKUPVALUE(User[EmployeeKey], User[Email], USERPRINCIPALNAME())). This checks if the logged-in user's ID exists anywhere in the path of the record being viewed. It’s highly scalable because you only need one single role defined in Power BI Desktop, and the logic automatically adapts based on whoever is viewing the report in the cloud.

   Answered 2025-02-05 by Patricia Hall

This approach makes sense for viewing data, but how does it impact the performance of the report if the hierarchy is 10 or 15 levels deep and we have thousands of employees?

   Answered 2025-02-08 by Kevin Peterson

• Performance shouldn't be an issue if your User table is indexed correctly at the source. The PATH function is calculated during the data load (Import mode), so the heavy lifting is done before the user even opens the report. During runtime, PATHCONTAINS is very efficient. We use this for a 50,000-person organization with 12 levels of management, and the overhead is barely noticeable. Just ensure your relationships between the User table and the Fact table are simple and direct.

     Commented 2025-02-12 by Michael Scott

Make sure you test this using the "View as Role" feature in Desktop while entering a specific user's email to verify the hierarchy logic works for different levels.

   Answered 2025-02-15 by Nancy Young

• Great tip, Nancy. Also, remember that RLS doesn't apply to Workspace Admins or Members—only to people with "Viewer" roles. I learned that the hard way!

     Commented 2025-02-18 by Patricia Hall