Dynamic RLS with USERNAME in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a Power BI developer at a retail company.

📋 Request: Your manager wants a sales report where each sales manager can only see data for their own region automatically.

📊 Data: You have a Sales table with columns: SalesID, Region, SalesAmount, SalesDate, and a Users table with columns: Username, RegionAssigned.

🎯 Deliverable: Create a Power BI report with dynamic Row-Level Security (RLS) so that when a sales manager opens the report, they only see sales data for their assigned region based on their login username.

Sample Data

SalesID	Region	SalesAmount	SalesDate
1	North	1000	2024-01-15
2	South	1500	2024-01-20
3	East	1200	2024-02-05
4	West	1300	2024-02-10
5	North	1100	2024-03-01
6	South	1400	2024-03-15

Username	RegionAssigned
[e-mail]	North
[e-mail]	South
[e-mail]	East
[e-mail]	West

1

Step 1: Load the Sales and Users tables into Power BI Desktop.

Use 'Get Data' to import both tables from your data source.

Expected Result

Both tables appear in the Power BI data model.

2

Step 2: Create a relationship between Users[RegionAssigned] and Sales[Region].

In the Model view, drag Users.RegionAssigned to Sales.Region to create a one-to-many relationship.

Expected Result

Relationship is active and visible between Users and Sales tables.

3

Step 3: Define a new RLS role named 'RegionFilter' with a DAX filter on the Users table.

In Modeling > Manage Roles, create role 'RegionFilter' with this DAX filter on Users table: [Username] = USERNAME()

Expected Result

Role 'RegionFilter' is created with filter that matches logged-in username.

4

Step 4: Test the RLS role by viewing the report as a specific user.

In Modeling > View as Roles, select 'RegionFilter' and enter '[e-mail]' as username.

Expected Result

Report shows only sales data where Region = 'North'.

5

Step 5: Build a simple sales report with a table visual showing Region, SalesAmount, and SalesDate.

Add a table visual with fields: Sales[Region], Sales[SalesAmount], Sales[SalesDate].

Expected Result

Report displays sales data filtered by the logged-in user's region.

Final Result

-----------------------------------------
| Sales Report (Dynamic RLS by Username) |
-----------------------------------------
| Region | SalesAmount | SalesDate        |
|--------|-------------|------------------|
| North  | 1000        | 2024-01-15       |
| North  | 1100        | 2024-03-01       |
-----------------------------------------

(If logged in as [e-mail], only North region sales show.)

✓Each sales manager sees only their assigned region's sales data automatically.

✓Dynamic RLS uses USERNAME() to filter data based on login.

✓This approach improves data security and report personalization.

Bonus Challenge

Extend the RLS to allow managers to see their region plus any sub-regions if applicable.

Show Hint

Create a hierarchy table for regions and modify the RLS DAX filter to include related sub-regions using PATHCONTAINS or similar functions.