Row-Level Security (RLS) in Power BI for organisational hierarchies

What is Row-Level Security (RLS)?
Row-Level Security (RLS) in Power BI restricts access to individual rows of data. Each user only sees the data for which they are authorised.

Why RLS for hierarchies?
RLS becomes more complex if the authorisations follow a multi-level organisational hierarchy. For example:

• • A manager can see his employees' data
  • A division manager can see all the data of his teams

• A normal employee only sees their own data

Basic principles of RLS

• Roles in Power BI:
  • Define filter rules with DAX expressions (True/False) per line.
  • Roles are assigned to users in the Power BI service.
  • RLS only filters rows, not columns or tables.
• Static vs. dynamic RLS:
  • Static: One role per authorisation level (poorly scalable).
  • Dynamic: Utilises functions such as USERNAME() or USERPRINCIPALNAME()to flexibly control roles at runtime (recommended).

Challenges with hierarchical RLS

• Managers should see all the data of their direct and indirect subordinates.
• Employees are not allowed to see data of colleagues at the same level or superiors.

Simple filters such as ManagerID = User are not enough here. The solution: parent-child hierarchies.

Parent-child hierarchy in DAX

An employee table contains:

• EmployeeIDEmployee ID
• ManagerID: Direct superior

Important DAX functions:

• PATH(EmployeeID, ManagerID) generates the entire hierarchy path.
• PATHCONTAINS(Path, UserID) checks whether a manager appears in the path.

Step-by-step implementation

1. prepare data model

Table structure example:

2. calculate hierarchy path

New calculated column:

Hierarchy Path = PATH(Employee[EmployeeID], Employee[ManagerID])

3. identify the current user

Create measure:

Current User ID = LOOKUPVALUE(Employee[EmployeeID], Employee[AD Account], USERPRINCIPALNAME())

4. define RLS role

New role with filter rule:

PATHCONTAINS(Employee[Hierarchy Path], [Current User ID])

5. testing and publishing

• Test role in Power BI Desktop (View as Role).
• Publish report in Power BI Service and add user to role.

Tips for optimisation

• Use dynamic instead of static RLS.
• Calculate the hierarchy path column in advance for better performance.
• Secure data quality: unique EmployeeIDs and correct ManagerIDs.
• Monitoring with Performance Analyzer to optimise performance.

With this approach, RLS can be implemented flexibly, efficiently and clearly.