Question

What’s the best way to implement dynamic security rules in Power BI Service based on user roles?
I need to set up row-level security (RLS) in Power BI Service to restrict data access based on user roles. What is the best approach using DAX functions like LOOKUPVALUE and USERPRINCIPALNAME, and how can I ensure that security roles dynamically adjust for different users?

Answer 1

Possessing up-to-date data until October 2023 enables you to most appropriately pursue the creation of dynamic implementation on Row-Level Security (RLS) in the Power BI Service.

1. Set Up a Security Table for Row-Level Security

Map a User Role Mapping Table, which includes the email addresses of users- UserEmail- and their roles or privileges- Role.

This table needs to be present in the Power BI Model and related to either the fact or the dimension tables.

2. Set Up Dynamic RLS DAX Filter

In Role Manager, use DAX expressions to create a security filter:

'UserRoles'[UserEmail] = USERPRINCIPALNAME()

If roles need to filter multiple levels, use LOOKUPVALUE:

VAR UserRole = LOOKUPVALUE('UserRoles'[Role], 'UserRoles'[UserEmail], USERPRINCIPALNAME())  
RETURN 'FactTable'[Role] = UserRole

• Use USEROBJECTID() instead of USERPRINCIPALNAME() when dealing with Azure AD groups for more secure mapping.

3. Assign and Validate Security Roles in the Power BI Service

• After publishing the report, go to Dataset Settings → Security, assign the role, and test using View As Role.

• Ensure users have only Viewer access in the workspace to prevent bypassing RLS.

• If using Azure AD security groups, manage access through group membership instead of adding individual users.