topic Re: Bypass RLS through DAX in Manage roles section in DAX Commands and Tips

Bypass RLS through DAX in Manage roles section

AnthonyJoseph — Tue, 12 Sep 2023 14:48:26 GMT

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

Re: Bypass RLS through DAX in Manage roles section

AnthonyGenovese — Tue, 12 Sep 2023 16:35:09 GMT

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!

Re: Bypass RLS through DAX in Manage roles section

AnthonyJoseph — Tue, 12 Sep 2023 17:16:43 GMT

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...

Re: Bypass RLS through DAX in Manage roles section

AnthonyGenovese — Tue, 12 Sep 2023 17:23:48 GMT

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!

Re: Bypass RLS through DAX in Manage roles section

AnthonyJoseph — Tue, 12 Sep 2023 17:29:05 GMT

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

Re: Bypass RLS through DAX in Manage roles section

MargusMartsepp — Tue, 12 Sep 2023 17:33:28 GMT

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:

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

Re: Bypass RLS through DAX in Manage roles section

AnthonyGenovese — Tue, 12 Sep 2023 17:34:19 GMT

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.

Re: Bypass RLS through DAX in Manage roles section

MargusMartsepp — Tue, 12 Sep 2023 17:39:24 GMT

I approve doing this with an Active Directory Group.