RLS - Allow certain users to see all.

[TonicQuake]

Hello,

New to RLS in PowerBI and hoping someone can help. 

I'm trying to setup Dynamic RLS but have an exception list of users that can access all data. 

Current I have a many - many, filter direction Both, relationship - UsersTable [Department] - DataTable[Department]

I've created a Security Role - [UserEmail] = USERPRINCIPALNAME() which works providing that the UserEmail is linked to a department in the DataTable. 

But I want those Users that have a department of HeadOffice to be able to view all data. 

[mari_pet]

@AllisonKennedy   or if anyone else is watching this thread.  This formula works but discovered that if I don't have other users assigned to the department the super user can't see what is in that department.

example:   

UserTable

user	dept
user1	dept A
user2	dept B
user3	Super

Dept

items	dept
item 1	dept A
item 2	dept B
item 3	dept C

Because no user is assigned to dept C  item 3 does not show in user 3(the super user)'s list when they look at the report.  I have assigned a  "fake user" to each of the departments to get around this but wasn't sure if there is something else that could be done either in the model or the below dax to address the issue.

The above tables relationship is mapped by "dept".

Under manage role, User is set to:  

VAR _role =
MAXX(FILTER( 'UserTable', 'UserTable'[UserPrincipalName] = UserPrincipalName()) , 'UserTable'[dept])

RETURN
IF( _role = "Super", TRUE(),
[UserPrincipalName] = UserPrincipalName()
)

[TonicQuake]

@AllisonKennedy  Thank you so much, the solutions is working. 

[AllisonKennedy · Accepted solution]

@TonicQuake  I see two options for you.

OPTION A: Create two roles

This option works if you already have a security group that contains all the head office users and can add that in PowerBI.com

Basic User Role

This is what you already have - filters the data based on department.

Head Office Role

Create a second role that has no filters. Add the Head Office people to this role in PowerBI.com 

OPTION B: Create a security filter mapping table

This option works if you do not have a security group for the head office users, and keeps all the settings in Power BI desktop. Only one role is needed, but you'll need a more complex DAX filter in the RLS:

All Users Role

VAR _Role = MAXX(FILTER( UsersTable, UsersTable[UserEmail] = USERNAME() ), UsersTable[Department])
RETURN
IF( _Role = "Head Office", TRUE()
,
[UserEmail] =
USERNAME()
)

[bmayankdw]

I used option A,

If there is a "super user" who can see multiple departments how would I ago about using a filter on the report? Is there anyway to hide the filter if there is only one selection available?