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. 

Zanqueta's avatar
Zanqueta
Super User
6 months ago

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 šŸŒ€.