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.

Practice

1. What is the main purpose of using USERNAME() in Dynamic Row-Level Security (RLS) in Power BI?

easy

A. To change the report theme dynamically

B. To filter data based on the logged-in user's identity

C. To calculate the total sales amount

D. To create new columns in the data model

Solution

1. Step 1: Understand USERNAME() function

   The USERNAME() function returns the current logged-in user's email or username.

2. Step 2: Apply USERNAME() in Dynamic RLS

   Dynamic RLS uses USERNAME() to filter data so each user sees only their allowed rows.

3. Final Answer:

   To filter data based on the logged-in user's identity -> Option B

4. Quick Check:

   USERNAME() filters data by user [OK]

Hint: USERNAME() filters data by current user identity [OK]

Common Mistakes:

• Thinking USERNAME() creates new columns
• Using USERNAME() to calculate totals
• Confusing USERNAME() with theme settings

2. Which of the following is the correct syntax to create a filter rule for Dynamic RLS using USERNAME() in Power BI?

easy

A. [UserEmail] = USERNAME()

B. FILTER([UserEmail], USERNAME())

C. USERNAME() = [UserEmail]

D. CALCULATE(USERNAME(), [UserEmail])

Solution

1. Step 1: Understand filter rule syntax

   In Dynamic RLS, the filter rule compares a column to the current user using =.

2. Step 2: Identify correct syntax

   The correct syntax is [UserEmail] = USERNAME() to filter rows matching the logged-in user.

3. Final Answer:

   [UserEmail] = USERNAME() -> Option A

4. Quick Check:

   Filter rule uses column = USERNAME() [OK]

Hint: Use column = USERNAME() for filter rules [OK]

Common Mistakes:

• Using USERNAME() on left side of comparison
• Using FILTER() function incorrectly
• Trying to CALCULATE USERNAME()

3. Given a table with a column UserEmail and a Dynamic RLS filter rule [UserEmail] = USERNAME(), what data will a user with email [e-mail] see?

medium

A. Only rows where UserEmail is not [e-mail]

B. All rows regardless of UserEmail

C. Only rows where UserEmail is [e-mail]

D. No rows at all

Solution

1. Step 1: Understand the filter rule effect

   The rule [UserEmail] = USERNAME() filters rows to only those matching the logged-in user's email.

2. Step 2: Apply to user email

   For user [e-mail], only rows with UserEmail = [e-mail] are visible.

3. Final Answer:

   Only rows where UserEmail is [e-mail] -> Option C

4. Quick Check:

   Filter shows rows matching USERNAME() [OK]

Hint: User sees only rows matching their email [OK]

Common Mistakes:

• Assuming user sees all rows
• Thinking user sees rows with different emails
• Believing no rows are shown

4. You wrote this RLS filter rule: FILTER(Users, Users[Email] = USERNAME()). Why does it cause an error in Power BI?

medium

A. FILTER cannot be used directly as a filter rule; it expects a boolean expression

B. USERNAME() is not allowed in RLS rules

C. The column name should be lowercase

D. FILTER requires two arguments

Solution

1. Step 1: Understand RLS filter rule requirements

   RLS filter rules expect a boolean expression, not a table expression like FILTER().

2. Step 2: Identify the error cause

   Using FILTER() returns a table, causing a syntax error in RLS rules.

3. Final Answer:

   FILTER cannot be used directly as a filter rule; it expects a boolean expression -> Option A

4. Quick Check:

   RLS rules need boolean expressions [OK]

Hint: Use boolean expressions, not FILTER(), in RLS rules [OK]

Common Mistakes:

• Using FILTER() instead of boolean condition
• Thinking USERNAME() is disallowed
• Assuming column case matters
• Believing FILTER needs two arguments

5. You want to create Dynamic RLS so that managers see data for their team members, and team members see only their own data. The table has columns UserEmail and ManagerEmail. Which DAX filter rule correctly implements this?

hard

A. USERNAME() = [UserEmail] && USERNAME() = [ManagerEmail]

B. [UserEmail] = USERNAME() && [ManagerEmail] = USERNAME()

C. FILTER(Users, [UserEmail] = USERNAME() || [ManagerEmail] = USERNAME())

D. [UserEmail] = USERNAME() || [ManagerEmail] = USERNAME()

Solution

1. Step 1: Understand the requirement

   Managers should see rows where they are the manager, and users see their own rows.

2. Step 2: Choose correct logical operator

   Use OR (||) to allow either condition: user is the row owner or user is the manager.

3. Step 3: Validate syntax

   [UserEmail] = USERNAME() || [ManagerEmail] = USERNAME() uses correct syntax and logic: [UserEmail] = USERNAME() || [ManagerEmail] = USERNAME().

4. Final Answer:

   [UserEmail] = USERNAME() || [ManagerEmail] = USERNAME() -> Option D

5. Quick Check:

   Use OR (||) to combine user and manager filters [OK]

Hint: Use OR (||) to combine user and manager filters [OK]

Common Mistakes:

• Using AND (&&) instead of OR (||)
• Reversing USERNAME() and column order
• Using FILTER() in RLS rule