Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
@TonicQuake I see two options for you.
This option works if you already have a security group that contains all the head office users and can add that in PowerBI.com
This is what you already have - filters the data based on department.
Create a second role that has no filters. Add the Head Office people to this role in PowerBI.com
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:
VAR _Role = MAXX(FILTER( UsersTable, UsersTable[UserEmail] = USERNAME() ), UsersTable[Department])
RETURN
IF( _Role = "Head Office", TRUE()
,
[UserEmail] =
USERNAME()
)
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@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 I see two options for you.
This option works if you already have a security group that contains all the head office users and can add that in PowerBI.com
This is what you already have - filters the data based on department.
Create a second role that has no filters. Add the Head Office people to this role in PowerBI.com
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:
VAR _Role = MAXX(FILTER( UsersTable, UsersTable[UserEmail] = USERNAME() ), UsersTable[Department])
RETURN
IF( _Role = "Head Office", TRUE()
,
[UserEmail] =
USERNAME()
)
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |