0
0
Power BIbi_tool~10 mins

Dynamic RLS with USERNAME in Power BI - Cell-by-Cell Formula Trace

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Sample Data

This table maps each user email to their allowed sales region for dynamic row-level security.

CellValue
A1UserName
B1Region
A2alice@example.com
B2North
A3bob@example.com
B3South
A4carol@example.com
B4East
A5dave@example.com
B5West
Formula Trace
FILTER('Sales', 'Sales'[Region] = LOOKUPVALUE('UserRegion'[Region], 'UserRegion'[UserName], USERNAME()))
Step 1: USERNAME()
Step 2: LOOKUPVALUE('UserRegion'[Region], 'UserRegion'[UserName], "bob@example.com")
Step 3: FILTER('Sales', 'Sales'[Region] = "South")
Cell Reference Map
    A              B
1 | UserName      Region
2 | alice@...     North
3 | bob@...       South
4 | carol@...     East
5 | dave@...      West

References:
- USERNAME() returns current user email
- LOOKUPVALUE uses A2:A5 and B2:B5 to find region
- FILTER applies region filter on Sales data
The USERNAME function uses the UserName column (A2:A5) to find the matching Region in column B2:B5 for filtering.
Result
Sales Table (Filtered for bob@example.com):

| OrderID | Region | SalesAmount |
|---------|--------|-------------|
| 102     | South  | 5000        |
| 108     | South  | 3200        |

Only rows with Region 'South' are visible to bob@example.com.
The final filtered sales table shows only rows where Region equals 'South', matching the logged-in user's allowed region.
Sheet Trace Quiz - 3 Questions
Test your understanding
What does USERNAME() return in this formula?
AThe sales amount
BThe region name
CThe email of the current logged-in user
DThe table name
Key Result
FILTER table rows where a column equals the region found by LOOKUPVALUE using USERNAME()