Forum Discussion
RLS - Allow certain users to see all.
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.
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()
)
4 Replies
- AllisonKennedy
Community Champion
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()
)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?
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()
)AllisonKennedy Thank you so much, the solutions is working.
