Forum Discussion
RLS - Allow certain users to see all.
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()
)
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()
)