A More Flexible Security Setup in Power BI
In a previous video, I covered how to set up Row-level Security for your reports in Power BI. This post is going to build on that foundation, so make sure that you start from there.
Row-level Security – BI-Lingual Analytics (bilingualanalytics.com.au)
In some cases, we don’t want to create hundreds of roles to suit business needs and to accommodate proper Data Security settings.
That’s when Dynamic RLS comes in handy!
With a little bit of DAX logic and a Users table to drive data restriction, we can create a pretty robust “access management” in Power BI.
If you want to learn how I arrived at the solution that I use make sure to watch the video.
DISCLAIMER
While I know that this logic works in Power BI, it even works with large-scale models, I believe that there is still some space to further optimise it.
So if you have any idea or suggestions on how to take it even further, please let me know!
Roland
🙏Thanks for watching and if you liked this video please hit the 👍 button.
📢 Also be sure to subscribe to the channel so you never miss a new video.
14 Comments
KUMAR · 9 May 2022 at 9:23 pm
Hey Hii,
Really helpful video,at end of the video you are mentioning about IN and OR usage but while i’m trying that kind of scenario that i’m getting below error message.
“Row Level Security – table of multiple values was supplied where a single value was expected”
how to rectify this one?please help me .
Thanks
bilinguala · 11 May 2022 at 7:10 pm
Hi there,
My solution would be something like this:
var _Rest =
CALCULATETABLE (
VALUES ( Users[RLS Product] ),
Users[E-mail] = USERPRINCIPALNAME()
)
var _RLS =
SWITCH(
TRUE(),
OR(
“All” IN _Rest,
[Group] IN _Rest
), TRUE(),
FALSE()
)
Return
_RLS
Hope this helps!
JJ · 15 July 2022 at 3:38 am
I could not make it work can you help me
JJ · 15 July 2022 at 1:11 am
Hi
Your video is amassing, it is really self explanatory and very useful. I would like to know if same user need to access multiple “RLS GRO” or “RLS Product” or RLS Customer” how the DAX need to be modified
bilinguala · 18 August 2022 at 6:24 pm
Hi there,
Potentially something like this – based on the DEMO file:
var RLS_Restriction =
SWITCH(
TRUE(),
OR(
“None” IN _Restriction,
[Customer] IN _Restriction),
TRUE(),
FALSE()
Gianluca · 22 July 2022 at 7:00 pm
Hey Hii;
I was looking at this very inspiring video and I have a question. You show how to use dax in roles to set, in the end, a true or false statement.
But which is/are the connection/s of the table with users and RLS permission to the rest of the model?
In other videos.
In other words, how the “User” table have to be connected to the rest of the model? other tutorial spend a lot on this while here I see nothing on this part
Thanks!
G
bilinguala · 18 August 2022 at 6:22 pm
Hi there,
There is no need to create any relationship with any other tables. We only use the “User” table in the roles.
Of course, if you want to further utilise the User table for slicing the report you can create relationships to other tables, but I wouldn’t recommend that as the data model could get ugly pretty quickly 🙂 Hope this helps!
Uju · 28 September 2022 at 11:06 am
Thank you so much, This really helped!!!
Georgiana · 12 January 2023 at 4:40 am
Hi, this is by far the easiest and most straight-forward method for dynamic RLS I found! Thanks a lot!
One question please – how to adjust if a user needs to see data for both “Electronics” and “Automotive”? I tried adding the same user two times in the Users file but it did not work.
bilinguala · 13 January 2023 at 8:24 am
Potentially something like this – based on the DEMO file:
var RLS_Restriction =
SWITCH(
TRUE(),
OR(
“None” IN _Restriction,
[Customer] IN _Restriction),
TRUE(),
FALSE()
Georgiana · 12 January 2023 at 10:45 pm
Hi! Thanks very much for sharing this approach! How would be best to adjust the methodology in case some users need to see both “Automotive” and “Electronics”? I tried adding the user twice in the Users table but this does not work. Perhaps an adjustment to the DAX code is needed instead?
bilinguala · 13 January 2023 at 8:24 am
Potentially something like this – based on the DEMO file:
var RLS_Restriction =
SWITCH(
TRUE(),
OR(
“None” IN _Restriction,
[Customer] IN _Restriction),
TRUE(),
FALSE()
arpit · 16 January 2023 at 11:07 pm
Awesome Tutorial, the best one on YouTube
But for me “None” is not working, when i manually check for USER3, its showing blank data. Instead of showing all data
my code
var _restriction =
calculatetable(
values( USERS[BFA] ),
USERS[E-mail] = USERPRINCIPALNAME()
)
var RLS_Restriction =
switch(
true(),
_restriction = “None”, true(),
[BFA] = _restriction, true(),
False()
)
return
RLS_Restriction
my table
E-Mail Name RLS Region Ship To BFA
USER1@abc.com USER1 EMEA IA
USER2@abc.com USER2 Asia Pacific PHC
USER3@abc.com USER3 None None
bilinguala · 17 January 2023 at 6:39 am
Thanks for the kind words!
Looking at the code (and the table) the only thing that I can think of is that in your RLS/User table you have an extra space (” “) after “None” – “None “.
I reckon your code is exactly the same as mine, just different headers (BFA), so I cannot see any issues quickly.
If that’s not the case, reach out via e-mail and pls send screenshot so I can try to identify what’s wrong.