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
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 id | user name | user email | Team |
1 | A | A@abc.com | Admin |
2 | B | B@abc.com | Employee |
3 | C | C@abc.com | Employee |
4 | D | D@abc.com | Employee |
5 | E | E@abc.com | Employee |
6 | F | F@abc.com | Admin |
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
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:
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
14 | |
14 | |
9 |