Forum Discussion

grkanth81's avatar
grkanth81
Helper II
6 months ago
Solved

Dynamic Row level security issue

Hi all, We are using Power BI Report Server. I am having some issues while implementing Dynamic Row Level security. The requirement is employees would need to see only thier related data in the das...
Dynamic row level security
Power BI Report Server
  • grkanth81's avatar
    grkanth81
    6 months ago

    Thank you all for your replies.

    I also tried using the below but this also didn't work.

    [Email] = USERPRINCIPALNAME()

    After much more investigation and google, got the below and used it and it seems to be working as expected.

    [Login] == LOOKUPVALUE('Employeetable'[Login], 'Employeetable'[Email], USERPRINCIPALNAME())

     

    Thanks all again for your inputs.

    grkanth81. 

Olufemi7's avatar
Olufemi7
Super User
6 months ago

Hello grkanth81, 

To make sure each employee only sees their own data, you can set up dynamic RLS by creating a role in Power BI Desktop and filtering rows based on the logged-in user. Microsoft explains this process in Row-level security (RLS) with Power BI Row-level security (RLS) with Power BI and RLS guidance in Power BI Desktop Row-level security (RLS) guidance in Power BI Desktop 

Step 1: Create a relationship

  • Connect Employee.EmployeeID → BilledHrs.EmployeeID.

Step 2: Define a role

  • In Power BI Desktop, go to Modeling → Manage Roles.

  • Create a new role (e.g., EmployeeRole).

  • On the Employee table, add a filter:

If your table stores domain logins (domain\JK, domain\SB):

[Login] = USERNAME()

If your table stores emails ([email protected]

[Email] = USERPRINCIPALNAME()

 

Step 3: Microsoft Documentation Quote

From Row-level security (RLS) guidance in Power BI Desktop – Microsoft Learn:

“The USERNAME function returns the domain name and username from the user credentials in the format domain\username. The USERPRINCIPALNAME function returns the user principal name (UPN), which is typically the user’s email address.”

This means:

  • In Power BI Report Server / Desktop, USERNAME() will give you domain\alias.

  • In the Power BI Service, USERPRINCIPALNAME() will give you the user’s email.

Step 4: Test the role

  • Use Modeling → View as Role in Power BI Desktop to simulate different users.

  • Confirm that each employee only sees their own rows.

Step 5: Publish to Report Server

  • After publishing, assign users to the role in Report Server.

  • RLS will apply automatically when they log in.

Example with Your Data

  • John Kerr (domain\JK) → sees only EmployeeID = 100 rows:

    • 12/01/2026 → 2.8, 3.5

    • 13/01/2026 → 7

    • 14/01/2026 → 0.5, 3.8

  • Steve Barr (domain\SB) → sees only EmployeeID = 200 rows:

    • 12/01/2026 → 7.5

    • 13/01/2026 → 7.5

    • 14/01/2026 → 2.6, 4

    • 15/01/2026 → 6, 7.5