RLS: overlapping rule / role filters

[Anonymous]

Hi all,

I have a little problem with RLS roles / filtering.

I successfully set up filters for the dozen or so departments that I need to show the data for, but my boss for example is head of our department - so he can see that data - but is also supposed to see ALL data, but the filter on our department seems to overrule the capability to see all data from the role without restrictions.

Any ideas how to solve that?

Thanks in advance!

[Anonymous]

Hi @Anonymous 

What kind of RLS did you build for your organization, static RLS or dynamic RLS?

If you build static RLS, please only add your boss into the Role without any restriction.

My Sample:

I build two rolse, Department and Boss. Department will restrict user only see values in Department and Boss without any restriction.

Publish the report add Tongzhou into Boss Role and add Eyelyn into Department Role in dataset security.

Result is as below.

Tongzhou in Boss role can see all values.

Eyelyn in Department role can onlu see department values.

Or you can change Boss's permission in your workspace. Just give him edit permission instead of viewer. Then your Boss can see all values in your report. And you don't need to add a new role.

If you want to build a dynamic rls by userprinciplename, you may refer to this blog: Dynamic Row Level Security with Organizational Hierarchy Power BI

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

I think that makes it a bit clearer, thanks!

When selecting "view as another user" in PBI, do I need to enter both the user's ID AND select the group I want to test? Because sometimes it showed me all data, despite the user ID in question not having permission.

[Anonymous · Accepted solution]

Hi @Anonymous 

If you use username() and userprincipalname() in your code, you are trying to build Dynamic RLS.

For reference: Dynamic RLS Sample

My Test:

Role:

RLS1 role is as below. And Boss role has no restriction.

View as is a test in Power BI Desktop to view things as a role.

If you select RLS1, you will be restrict by your userprincipalname. As below, you can only see values belong to User1.

If you select Boss, you can see all things no matter who you are. Don't forget to add the users in their correct role in Security in Power BI Service.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

I tried using Security in the online service, but there it said I should manage that in the program itself. =(

[DataZoe]

@Anonymous You can set up a new role for him, that doesn't limit the data. The roles will work like you can see this (role 1) AND this (role 2).  

[Anonymous]

@DataZoeThank you for the idea, but I already tried that. He's in a group without filters and in the "department" group with the department data filter and his ID against a department access list with userprincipalname(). But for some reason that lets him only see our department, not all data.