Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
TonicQuake
Regular Visitor

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. 

 

 

 

 

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

@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()
)


Please @mention me in your reply if you want a response.

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

View solution in original post

4 REPLIES 4
mari_pet
Helper I
Helper I

@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

userdept
user1dept A
user2dept B
user3Super

Dept

itemsdept
item 1dept A
item 2dept B
item 3dept 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
Regular Visitor

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

AllisonKennedy
Super User
Super User

@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()
)


Please @mention me in your reply if you want a response.

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? 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.