By Pavani Dumpala, HEXstream data analyst 

In Power BI, row-level security (RLS) plays a pivotal role in restricting data access at the user level, ensuring that individuals only see the data relevant to their role or permissions when interacting with the reports. One of the most powerful and flexible implementations of RLS is dynamic row-level security, particularly when managing complex organizational structures.

For instance, a manager can view data for their team, but they won’t be able to access information from other departments. This ensures that each user only sees the data relevant to them, keeping sensitive information secure and maintaining data privacy across the organization.

In this blog, we will explore dynamic row-level security within the context of an organizational hierarchy, using Power BI’s capabilities to tailor data visibility for users at different levels. 

Organizational hierarchy

To manage and analyze hierarchical data in Power BI, where rows are linked by a parent-child relationship, specialized parent-child DAX (Data Analysis Expression) functions play a crucial role. Some commonly used functions for managing hierarchical data include:

PATH: Returns a delimited text which contains all the parents to the current row.

In our example, on a table that contains employees, the managers of employees, and the managers of the managers, you can return the path that connects an employee to his/her manager. 

DAX Syntax : PATH(Child ID, Parent ID) 

PATH CONTAINS: Checks whether a specified identifier exists within the hierarchy path.

DAX Syntax : PATH CONTAINS(PATH(Child ID, Parent ID),Parent ID))

This checks if a specific parent is part of the hierarchy chain for the child.

PATH ITEM: Helps in extracting an item at a specific level within the hierarchy.

DAX Syntax : PATHITEM(PATH(Child ID, Parent ID), 3)

This returns the item at the third level of the hierarchy for a given child from the top.

PATH LENGTH: Returns total number of levels in the hierarchy path generated by the PATH function.

DAX Syntax: PATHLENGTH(PATH(Child ID, Parent ID))

Example scenario

In this scenario, we explore how to implement dynamic row-level security (RLS) in Power BI for an organization that tracks both employee hierarchy and sales performance. The goal is to ensure that each employee (particularly managers) can view the sales data of their team while restricting access to only relevant data based on their position in the hierarchy. Employees should not be able to view their managers data.

Data structure

Employee table contains the following:

Sales table contains the following:

The employee and sales tables are linked via the employee ID column.

Requirement

• Employees can only view their own sales data.
• Managers can view their team’s sales data, including any employee reporting to them at
  any level.
• No employee should be able to view their manager’s sales data.

Implementation

1. Employee table setup: Add a calculated column to the employee table to generate the hierarchy path for each employee, which shows the chain of managers above them. You can use the PATH function to create a UserPath: 

This creates a path that shows all the employee IDs of the managers for each employee.

2. Defining RLS in Power BI : The RLS is implemented through DAX expressions in the “Manage Roles” section of the Power BI. When an employee signs in, the model dynamically adapts to the logged-in user’s email address, ensuring data is filtered according to their role. The logic also ensures:

• An employee can see only their own sales data.
• A manager can see their team’s data but not their manager’s.

To do this go to Modeling>Manage Roles>Create new role. Here a new role created named Dynamic RLS and under Employee Data created the DAX.

USER PRINCIPLE NAME: Returns the logged-in user’s email address.

Filter: Filters the employee table to identify the row corresponding to the logged-in user based on their email ID.

MAXX: Retrieves the employee ID of the logged-in user.

PATH CONTAINS: Checks if the logged-in user’s employee ID is contained within the UserPath of other employees. This ensures the user can only see data for employees reporting to them.

Result

Now when a manager logs-in, Power BI recognizes the hierarchy defined by the UserPath and allows the manager to view sales data for all the employees reporting to them, including direct and indirect reports.

Here under the “View as” section I have given an email ID of a manager, and the results are as below giving the employees under that manager.

The roles are initially defined in Power BI Desktop, and once the report is published, they are enforced in Power BI Service to control data access dynamically.

To do that, in Power BI Service navigate to Dataset’s security settings where you can assign users or groups to the RLS roles. By doing this, we are applying data access restrictions at the dataset level. 

Final insights

This setup ensures that data access is dynamically controlled based on the logged-in user’s email. This enables organizations to securely manage access to sensitive data based on an employee’s role and reporting structure. 

CLICK HERE TO CONNECT WITH US ABOUT OPTIMIZING YOUR POWER BI STRATEGIES.