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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AnthonyJoseph
Resolver III
Resolver III

Bypass RLS through DAX in Manage roles section

Hello Community,

 

In our dashboard are currently using RLS (user email = userprincipalname()) to show data based on the logged in user. Now, we need to bypass this RLS when the user is from "Admin" team.

 

I have pasted below the sample data:

user iduser nameuser emailTeam
1AA@abc.comAdmin
2BB@abc.comEmployee
3CC@abc.comEmployee
4DD@abc.comEmployee
5EE@abc.comEmployee
6FF@abc.comAdmin

 

For Example: when user A or F logs in then RLS should not be applied i.e. The user should be able to see all data. when the other users except A and F log in , they should see only their data. 

 

Please can someone help in this regard.

 

Thanks,

AnthonyJoseph

7 REPLIES 7
MargusMartsepp
New Member

You can use an OR condition in the DAX formula to check if the user is part of the "Admin" team. If they are, the RLS filter will not be applied.

= 
(
    [user email] = USERPRINCIPALNAME() 
    || 
    LOOKUPVALUE('Admin_RolesAndPermissions'[Team], 'Admin_RolesAndPermissions'[user email], USERPRINCIPALNAME()) = "Admin"
)

Just as a reminder to implement this, you would:

  1. Go to the "Modeling" tab in Power BI Desktop.
  2. Click on "Manage roles."
  3. Create a new role or edit an existing one.
  4. Paste the DAX filter expression next to the table where you want to apply RLS.
  5. Save and close the "Manage roles" window.
  6. Before publishing, you can view the report as roles to test the RLS.
AnthonyGenovese
Resolver III
Resolver III

It isn't bypassing RLS. You create a new roll that doesn't have the username filter on it. This would be great as an AD group.

But if you really wanted to do it all in one, do something like an OR(user email = userprincipalname(). Team="Admin")

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!

Thanks @AnthonyGenovese  I tried the OR([user email] = userprincipalname(), [Team]="Admin") but its not working as expected i.e. its reacting the same as [user email] = userprincipalname().

 

If I create another role, will this ignore the existing [user email] = userprincipalname() RLS? not sure what I am missing...

 Yah sorry. Its not exactly team="admin" I think you would have to write dax that looks at the userprincipal name, does a lookup to see if they are an admin, and if so, then returns true on the second part of that OR statement.

 

Personally, I would go the route of a different role. That is what it is there for. It is what we implement in our many RLS instances.  The greatest thing about RLS is that security is additive. So if you have one restricted like you have, and then another role where someone gets full access, then the user does get full access.  Basically, if you have multiple roles, and a user is in multiple roles, as long as they have access in one of those roles, then they have access.

 

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!

Thank you for sharing your thoughts @AnthonyGenovese ... I m not sure if I understand about multiple roles w.r.t. to "Admin" and "Employee" view in RLS, if you can provide some sample or guidance that would be much helpful.

 

Thanks,

AnthonyJoseph

They way we do RLS for admins, is we have an AD Group that all the admins are in. Then, we add a new role call admin, with read privilages. Add that AD group to that role and you are all done. 

 

If you have to use a table like you described to find who the admins are, you have to do it in dax. 

I approve doing this with an Active Directory Group.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors