Dynamic Row level security issue

[grkanth81]

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 dashboard. This is what I have tried so far and here is the below sample dummy data to give rough idea:

BilledHrs table

EmployeeID	TranDate	BilledHrs
100	12/01/2026	2.8
100	12/01/2026	3.5
100	13/01/2026	7
100	14/01/2026	0.5
100	14/01/2026	3.8
200	12/01/2026	7.5
200	13/01/2026	7.5
200	14/01/2026	2.6
200	14/01/2026	4
200	15/01/2026	6
200	15/01/2026	7.5

Employee table

EmployeeID	Name	Login	Email
100	John Kerr	domain\JK	John.Kerr@company.com.au
200	Steve Barr	domain\SB	Steve.Barr@company.com.au

So, when employee JK views the dashboard they should see only thier billed hours and when employee SB views the dashboard they should see thier billed hours only.

After publishing dashboard normally if any user wants to have access to the dashboard we give their AD login access to that dashboard i.e. domain\login to that dashboard and they will have access to that dashboard.

So, I have created a new role named login and I have filtered to the Employee table - Login = USERNAME() and published it to the report server. And in the report server, there is a AD group named domain\AllEmployees. So. under RLS I have created this domain\AllEmployees and mapped to the Login role. But when viewing the dashboard I couldn't see any data relating to my login JK.

I have noticed that USERPRINCIPALNAME() is returning email address and USERNAME() is returning local windows account (domainname\login) in Power BI desktop. But after publishing to the report server, both USERPRINCIPALNAME() and USERNAME() are returning email address only and this is where my row level security seems to be not working I guess.

Can you please let me know if I am doing anything wrong and point in the right direction ?

Thanks

grkanth81

[grkanth81 · Accepted solution]

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. 

[Zanqueta]

Hi @grkanth81, just to contribute in this topic.

This issue relates to how Power BI Report Server interprets the functions USERNAME() and USERPRINCIPALNAME() after publishing. This behaviour differs from the Power BI Service in the cloud.

What is happening?

• In Power BI Desktop, USERNAME() returns domain\login and USERPRINCIPALNAME() returns the email address.

• In Power BI Report Server, both functions return the email address (UPN) because Report Server uses Windows Integrated Authentication and maps to the Active Directory UPN.

Therefore, when you create the rule Login = USERNAME(), it does not match because your Employee table contains domain\login, whereas the function returns an email address.

How said @Olufemi7, adjust the column used for RLS

[Email] = USERPRINCIPALNAME()

And follow the comment @tayloramy  

• Always use USERPRINCIPALNAME() for enterprise environments, as it is more consistent with Active Directory and Azure AD.

• Ensure the security table (Employee) uses the same format as the function output.

• Test in the final environment (Report Server), as behaviour differs from the Power BI Service.

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

[Olufemi7]

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 (John.Kerr@company.com.au

[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

[tayloramy]

Hi @grkanth81, 

Generally you will want to use USERPRINCIPALNAME() and user's email's for RLS. 

I've never had much success with USERNAME() as it behaves inconsistently depending on how the report server is set up.

If you switch over to using USERPRINCIPALNAME() and filtering on the email column, your set up should work well. 

One other thing to keep note of is that the table where you are filtering RLS on needs to have relationships to the other tables in your model for them to be filtered.