Dynamic Row Level Security in Power BI: Managing Multiple Roles
Row-Level Security (RLS) in Power BI is a critical feature for enhancing data security and ensuring that sensitive information is accessible only to authorized users. In today’s data-driven environment, where business intelligence tools like Power BI are extensively used to make informed decisions, managing data access becomes paramount. RLS plays a vital role in this context by providing a robust mechanism to control data visibility at a granular level.
Understanding Row-Level Security (RLS)
RLS allows report designers to create rules that dynamically filter data based on the identity and roles of the users accessing the report. This means that different users can see different views of the same report, depending on their access rights. For instance, a regional manager might only see data relevant to their region, while a department head might see data across multiple regions but only for their specific department.
Scenario: Dynamic Sales Data Access in a Corporation
Background:
Company: A large corporation with multiple departments and regional offices.
Data: A central Power BI report containing comprehensive sales data.
Users: Sales representatives, regional managers, and department heads.
Requirement:
Sales representatives should only see data related to their own sales.
Regional managers should see data for all sales representatives in their region.
Department heads should see data across multiple regions but only for their specific department.
Our tables:
Sales table
This table contains records of sales transactions. Each record includes details about the sales representative, the region, the department, and other relevant sales information.
Roles table
This table contains information about each user, including their email, role, department, and region. This table is crucial for implementing dynamic RLS.
These two tables have to be joined with UserEmail — SalesRepEmail columns
Notes on the Tables:
Sales Data Table: Contains the transactional sales data. Each row is a sale, tagged with the sales representative’s email, the region of the sale, the department, and other sales details.
User Information Table: Contains user profiles. Each user has a designated role (e.g., SalesRep, RegionalManager, DepartmentHead), and is associated with a specific department and/or region. The empty cell in the department or region indicates that the user has cross-departmental or cross-regional access (like for Regional Managers and Department Heads).
Define RLS Roles:
For Sales Representatives, the filter formula on the Sales Data Table is: Sales[SalesRepEmail] = USERPRINCIPALNAME() — which translates to “when user logs in use its email to allow him see records in Sales table where email matches the one used to login”Simply each sales rep can see sales where he is listed in the Sales table
For RegionalManager role the filter formula on the Sales Data Table is: Sales[Region] = LOOKUPVALUE(User[Region], User[UserEmail], USERPRINCIPALNAME()) — which translates to “when user logs in with email check what region is assigned to that user in Sales table”
For DeptManager role the filter formula on the Sales Data Table is: Sales[Department] = LOOKUPVALUE(User[Department], User[UserEmail], USERPRINCIPALNAME()) — which translates to “when user logs in with email check what Department is assigned to that user in Sales table
Assigning roles in Power BI Service
Assigning users to Row-Level Security (RLS) roles in Power BI Service is a crucial step in ensuring that your data security policies are correctly implemented. Here’s a detailed guide on how to do this:
Prerequisites
Before you begin, make sure that:
You have already published your Power BI report to the Power BI Service.
RLS roles have been defined in your Power BI Desktop file.
You have a list of users or security groups along with the roles they should be assigned to.
Steps to Assign Users to RLS Roles in Power BI Service
Open Power BI Service:
Go to Power BI Service.
Sign in with your credentials.
Navigate to the Semantic Model:
In the workspace where your report is published, locate the semantic model you applied RLS to.
IMPORTANT: RLS is set at the semantic model level, not at the report level.
Access Semantic Model Security:
Click on the More options (…) button next to the semantic model.
Select Security. This will open the RLS security settings for that semantic model.
Assign Users or Groups to Roles:
You will see a list of roles you defined in Power BI Desktop.
For each role (see screenshot below):
Click on the role to open its details.
In the text box under Members, start typing the name or email address of the user or security group you want to assign to this role. Power BI Service will auto-suggest matching users and groups.
Select the correct user or group (users in our case). You can add multiple users or groups to the same role.
After adding all the necessary users/groups, click Add.
Save Changes:
After assigning users to roles, make sure to save your changes.
Repeat the process for each role you have created.
Let’s test our roles logging with different users:
Logging in as user4 (RegionalManager) — expected to see all data for assigned North Region:
Works as expected!
Same for user5 (DepartmentHead) — expected to see all data for assigned Electronics department (check the roles table)
Again works as expected only data for Electronics is shown!
Notes:
If you see this message after creating roles in Power BI it most likely means you have not assigned roles to users and groups in Power BI service.
Thank you — I hope you found this article useful!
Thank you for this very complete article. I implemented a dynamic RLS in my report exactly this way but it seems that users who have access to the workspace but do not have roles, can access the report and see all the data. How can I block their access to the report?