0
0
Power BIbi_tool~8 mins

Dynamic RLS with USERNAME in Power BI - Dashboard Guide

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Dashboard Mode - Dynamic RLS with USERNAME
Business Question

How can we show sales data only for the logged-in user using dynamic row-level security (RLS) with the USERNAME function?

Sample Data
UserEmailRegionSalesAmountOrderDate
alice@example.comNorth10002024-01-15
bob@example.comSouth15002024-02-20
alice@example.comEast7002024-03-05
carol@example.comWest12002024-01-30
bob@example.comNorth9002024-04-10
Dashboard Components
  • KPI Card: Total Sales for Current User
    Formula:
    Total Sales = SUM(Sales[SalesAmount])
    Shows the sum of sales filtered by the logged-in user's email.
  • Table: Sales Details
    Columns: UserEmail, Region, SalesAmount, OrderDate
    Shows only rows where UserEmail matches the logged-in user due to RLS.
  • Bar Chart: Sales by Region for Current User
    X-axis: Region
    Y-axis: Sum of SalesAmount
    Filtered dynamically by RLS to show only current user's data.

RLS Role Filter Expression

Sales[UserEmail] = LOWER(USERNAME())

This expression filters the Sales table rows to only those where the UserEmail matches the logged-in user's email.

Dashboard Layout
+----------------------+----------------------+
|      KPI Card        |      Bar Chart       |
|  Total Sales Amount  | Sales by Region      |
+----------------------+----------------------+
|                  Sales Details Table             |
+-------------------------------------------------+
Interactivity

The dynamic RLS filter automatically applies based on the logged-in user's email. No manual slicers are needed to filter data by user.

All visuals (KPI card, bar chart, and table) update automatically to show only data for the current user.

Self Check

If you log in as bob@example.com, which rows appear in the Sales Details table?

Answer: Only rows where UserEmail is bob@example.com (rows 2 and 5 from the sample data).

All visuals update to show sales totals and regions only for Bob's data.

Key Result
Dashboard showing sales data filtered dynamically by logged-in user using USERNAME-based RLS.