Dynamic RLS with USERNAME in Power BI - Step-by-Step Guide

Introduction

Dynamic Row-Level Security (RLS) with USERNAME lets you show each user only the data they are allowed to see. It uses the logged-in user's name to filter data automatically, so you don't have to create separate roles for every user.

When you want salespeople to see only their own sales data in a shared report.

When managers should view data only for their own teams without extra copies of the report.

When you have many users and want to avoid creating a role for each one manually.

When you want to secure sensitive data by user identity in a dynamic way.

When you want to personalize dashboards based on who is logged in.

Steps

Step 1: Open

- Power BI Desktop

Your report file is open and ready for editing

Step 2: Go to

- Modeling tab > Manage Roles

The Manage Roles window opens showing existing roles

Step 3: Click

- Create

A new role is added for editing

Step 4: Name

- New role

The role is named, for example, 'DynamicUser'

Step 5: Select

- Table that contains user emails or usernames

The filter expression box is active for that table

Step 6: Type

- Filter expression box

The role filters data dynamically based on logged-in user

💡 Use the expression: [UserEmailColumn] = USERNAME() or [UserEmailColumn] = USERPRINCIPALNAME() depending on your data

Step 7: Click

- Save

The dynamic role is saved and ready for testing

Step 8: Go to

- Modeling tab > View as Roles

You can test the role by entering a username to see filtered data

Before vs After

Before

Report shows all sales data for all users without filtering

After

Report shows only sales data where the UserEmailColumn matches the logged-in user's email

Settings Reference

Role filter expression

📍 Manage Roles window, filter expression box

Defines which rows each user can see based on their login name

Default: No filter

View as Roles

📍 Modeling tab

Allows previewing the report as if logged in as a specific user

Default: No role selected

Common Mistakes

Using USERNAME() when your data stores emails but USERNAME() returns domain\username format

The filter will never match because formats differ

Use USERPRINCIPALNAME() which returns the full email address to match your data

Not testing the role with 'View as Roles' after creating it

You won't know if the filter works correctly until you test

Always use 'View as Roles' and enter test usernames to verify filtering

Summary

Dynamic RLS with USERNAME filters data based on who is logged in.

It saves time by avoiding manual role creation for each user.

Remember to match USERNAME() or USERPRINCIPALNAME() format with your data.