Dynamic Row-level Security 🔐 - Based on Dimension Tables
HTML-код
- Опубликовано: 2 авг 2024
- Dynamic RLS provides a more flexible option in Power BI when it comes to Security Settings.
It utilizes the USERPRINCIPALNAME function to pick up who's logged in, and a bit (or a lot of) DAX logic.
I really love this setup as it helps to drive real-world security options without adding too much complexity and performance overhead to my models.
If you also want to learn how to create RLS based on any dimension tables and any number of queries, make sure to watch till the end!
Have a good one,
Roland
💪 New videos coming every Tuesday 🤹♂
🤘 New short videos coming every Friday 👨🏫
📝 BLOG POST: bilingualanalytics.com.au/dyn...
🎬 MORE VIDEOS:
Power BI Row-level Security - Complete Guide - • Power BI Row-level Sec...
Power BI & Power Automate - How to automate reporting process - • Power BI & Power Autom...
How to prepare for the DA-100 Exam - My journey, learning materials & what to expect - Power BI Tips - • How to prepare for the...
🔗 LINKS:
MS RLS Guidance - docs.microsoft.com/en-us/powe...
Chapters:
00:00 Intro
00:36 Dynamic RLS
01:01 Topics Covered
01:47 Problem To Solve - Issues
02:41 Users Table
03:16 Basics
03:58 Role #1 - TRUE/FALSE
04:31 Role #2 - VARIABLE
05:08 Role #3 - Manage No Restriction
06:02 Role #4 - Combine #2 and #3
06:54 Role #5 - Optimize DAX
07:44 Quick Summary
08:21 Role #6 - Pick-Up User's Restriction
10:00 Role #7 - Optimize DAX 2
10:36 What's Next?
11:19 Role #8 - Expand The Logic
12:00 DEMO
12:30 How To Manage Dynamic RLS
13:02 DAX - Performance To Keep In Mind
13:37 Flexible Logic
13:55 HIDE Users Table !!!
14:12 Food For Thought
15:02 Summary
15:19 Question Of The Day
15:36 End
▼▼▼▼▼▼▼▼▼
Make sure to hit the 👍 button and ❗❗ SUBSCRIBE ❗❗ to my channel.
If you have any questions, just let me know either in the comments down below 👇 or send us an e-mail 📩.
▲▲▲▲▲▲▲▲▲
🤝 HOW TO CONNECT 🤝
LinkedIn - / bilingualanalytics
Twitter - / bilanalytics
Subscribe - / @bilingualanalytics
ABOUT ME: bilingualanalytics.com.au/abo...
📍 - Sydney, Australia
📽 GEAR:
📷 - Canon EOS 200D
🎙 - Audio Technica AT2020
🔦 - Neewer 18” Ring Light
🎧 - BOSE QC35
🖥 - 2x ASUS VC279H
💻 - AMD Ryzen 3600, Gigabyte X570, GTX1650, G.Skill 32GB RAM
Music: www.bensound.com
#PowerBI #DynamicRLS #RowLevelSecurity #BILingualAnalytics #DataSecurity 
Наука
Thanks for all the questions about how to expand this Dynamic RLS setup to have multiple groups available per user.
First of all, you need to add a row to each security setup per user and then adjust the _RLS variable to this:
var _RLS =
SWITCH(
TRUE(),
OR(
"All" IN _Rest,
[Group] IN _Rest
), TRUE(),
FALSE()
)
But when do you do "ALL" IN _Rest what do you mean with ALL
@@MichaelHernandez-tl7jy that's what I have in my users table "All" or "Electronics" or "APAC"
It's based on email ids per user and i have created a seperate table which is region wise and into many to many relationship by using dax userprincipalname ()
@@BILingualAnalytics can you please post one video on dynamic RLS and issues faced while working
super cool, that's the one I need. it works pretty gooddddd thankssss
Dude, thank you for this video! I had to implement RLS at work and couldn't figure out Microsoft documentation. Helped me a lot!
It could be tricky to implement some more complex RLS setups, but glad to hear it helped you!
Your solution is working beautifully, thanks!
This is the exact video I am looking for as I have been struggling for days with Dynamic Row Level Security... Yay!!
Happy to hear that! :)
This is top teir. Appreciate the knowledge!
This was awesome! Thank you - I had to watch it several times before I could implement it - this will save a lot of AD-groups!
Most definitely! Especially if you have some complex setups - who would want to create a group for one or two people, right? :)
Thanks for sharing this information,it will help and will come back if anything.🙏
Man I love it! 100000% useful!
i just love you for this solution. literally saved my life. THAAANKS!
Glad it helped!
Wow, GREAT video! Thank you very much for this video. I have a question about the table un Excel with the users and their secure level of each column, does it need to have a relationship in the model? Very useful ;)
Thank you so much. This saved me!!!!
Great video Thank you!
Great video and channel! You got a new follower =))
Thanks for a nice flow. Can this technique be used for direct query as an option? an you please suggest.
Hi! Great video!!! really usefull.
One question, how can de switch statement be modified to evaluate more than one column from the same table?
Thank You so much!!!!
I'm happy you found it useful!
Yep, very! Very well presented. As we all know any manual from MS is absolutely useless and understandable only by an author. Here you have explained it in a very nice way. Thx.
Works really great, thank you !
How can we implement RLS for multiple properties within one single dimension table ? For example Product Name / Color / Group with both Color and Group in the same DIM PRODUCT table, with PERSON1 in charge of all products that are RED / ELECTRONICS ?
Really great 😀
Hello, congratulations for the content.
I have a question, if I need to filter different Categories, in the same column, is it possible?
Example, user can access, Electronics, marketing, except Payroll.
Thank you for sharing... its great content... I have a question however, when writing the VAR function, what does the underscore mean ie _Restriction? hope you can assist.. thanks !
Hello Boss, it's super powerful.. One simple question how can we implement this using AD group..
Please have you been able to build the one with a user existing more that once. The one you were talking about using 'in'
This is amazing. I am still confused about how this actually works.... I have 15 different contracts that I am trying to filter the data by. Do I just remove all of the filters that would filter the data by contract and then the variable will just update everything?
Thank you very much for this amazing tutorial :)
I just have one query can you please show how to filter multiple columns of a single table? It''ll be a great help
Please can you respond this question? Your video is great!
Any thoughts on how to set up the same logic, but instead of "AND" statements use "OR" statements? For example with Sri Lorin:
• AND: Department = Marketing AND Product = Computers AND Geo = APAC AND Customer = Microsoft
• OR: Department = Marketing OR Product = Computers OR Geo = APAC OR Customer = Microsoft
I have something using surrogate keys, but wondering if there's any elegant DAX options?
I have an excel where I have different project information from Jira and along with project related data ,I have Col1 for Requestor id information,Col2 for AVP information,Col3 for VP information and col4 for director information.Order is Requestor >AVP>>VP>>>Director
So requirement is when a "Requestor" logs in , he can see only projects belongs to him.When an AVP logs in then he can see all requestor who reports to him and their project information.Similarly when a VP logs in then he can see all AVP and Requestor who reports to him and their project information and so on..How to do this in Power BI ?
Hello, thanks for the video. It explained very well the dynamic RLS. But I have an issue. What is I need an "OR" condition instead of "AND". For instance, a person X has access to all "APAC" region (no matter what the product is) and to the product "Electronis" no matter the region is.
It is a bit of complex to do (I assume) but it is needed in some business, for a person responsible of a region and of a product type (or a business unit).
Could you please help ?
I would use an OR statement, something like this:
var _RLS =
SWITCH(
TRUE(),
OR(
"All" IN _Rest,
[Group] IN _Rest
), TRUE(),
FALSE()
)
Thank you for this, what if a user requires multiple Departments x 3 - do i need to list the user 3 times on the backend, one for each department?
@@BILingualAnalytics can you advise how to adjust DAX?
This is great! Didn’t think it would work, but I got it to work finally. Unfortunately now I realized I need to base my RLS on two columns within the same dimension table instead of 2 different table. How do you accomplish this?
When it comes to setting up the RLS instead of jumping over to another table just select a different field in the same table.
it will add a "&&" line so apart from that everything should work fine :) Hope this helps!
@@BILingualAnalyticsany sample syntax?
Pursuit of the knowledge In this video has made me go to unbelievable heights. Now that I’ve found it, I can’t thank you enough!! When I click “Manage Roles” the Tables field has no tables. How do I have this field populated? Please help!
Thanks for the response! I finally saw it! I had shied away from clocking “Create” button under Manage roles. Thanks so much once again. One more thing you forgot to mention that relationships between the tables ought to be bidirectional.
Hi, thanks for this video!
The switch statement always returns TRUE, so how come the data gets filtered?
@@BILingualAnalytics Thanks. What I don't understand is that:
[Group] = _Restriction, TRUE() - returns TRUE, so I would expect to see all the data, but somehow it works as you intended and the data gets filtered.
Hello, that was a very good explanation of how the RLS works.
I have a question: I have a business requirement in which users have the possibility to compare themselves against the aggregated data of their country/division, without seeing the concrete detail per plant. Do you think it is possible to "skip" the RLS to create this concrete measure?
Thank you very much😉
@@BILingualAnalytics
Yes, that's the idea. To give users the data of their plant and the global data of the company, to make a comparison of their values against others, but without seeing the specific data of other plants.
Thank you!
Hola Miguel! Tengo que hacer un report con el mismo requisito. Encontraste solución a este problema?
I did an implementation of a similar scenario. You need to create another table with aggregated values and limited columns (as per your requirements). Then create necessary relationships and DON'T apply RLS on this new table, so all users can have access to all the data.
I hope it helps
Very good explanation, thank you.
I am working on a challenge similar, more elaborate in two ways.
A - the user should see all products in home organisation (via organisation dimension) and all products of products of specific product line over all sales organization.
The code in your comment of two weeks ago suggests an solution by expanding the code in the video with OR. Correct? Question, would it also work by adding a second role? So one role for the home organisation with all products and one role of the product line with all organizations?
B - the user might have to see multiple product lines and or organisations.
Would the solution be to use IN in the switch formula; with multiple lines in the user table (via m) ?
Thanks for the kind words!
Well, let me try to answer your questions - hoping that I understand the desired outcome.
Re A- if I understand correctly the security in scenario A is twofold. Let me try to use the video as an example. You need to limit access by "Channel" and by "Products" tables. By default, users should only see the channel they are working in. BUT they can also see the Products they are responsible for ALL channels. Is that right?
While RLS is additive, meaning that if you are in multiple profiles you will see ALL of the profiles, in the given scenario you also need to find a way to add an additional group to the "Channel" table security profiles to allow not only 1st level of responsibility but also those channels where a set of products were sold.
That's an interesting setup, so if you can confirm that my understanding is correct I'd need to work on it a bit. I'm planning on recording another video to answer the amazing questions that are asked here. I probably add this one as well.
Re B - correct. That was an easier one than A :)
@@BILingualAnalytics Any updates on Point A im facing the same challenge is there any tip to handle this case ?
@@iliassbz3625 I haven't had a chance to finalise it, but I have something in the working. Stay tuned!
It's very nice and i can see different comments using Switch case etc based on all inputs in comments can you make another dax query with this using same method implement different page levels access can you
@@BILingualAnalytics thanks for comment for me now its an requirement that needs rls based on product and page restrictions to your knowledge and now can you help me to achieve the requirements
Hi and thanks for the sample,
What about the case that I want to filter by multiple columns and rows, ie I want some user to be filter by 2 market (LATAM and EU) and by category (Technology).
In this case we need 2 or more rows by dimension for the same user !
Your RLS architecture doesn't support this case, I try by myself to arrange your code but still don't get the result that I need.
Think its possible ?
thanks in advance,
BR
Julien
I would use an OR statement, something like this:
var _RLS =
SWITCH(
TRUE(),
OR(
"All" IN _Rest,
[Group] IN _Rest
), TRUE(),
FALSE()
)
@@BILingualAnalytics Hi, thanks for your answer, I tried your solution but I can't achieve the right result, why use a OR statment in our case ? We want to cumulate filtering by dimensions so it should be AND am I right ?
Thanks in adavance !
Julien
How can i use RLS to mask the data in a column? Or return the column with blank values?
RLS and DataMasking are two distinct things. If you want to make sure that users can only see data they are allowed to see use RLS. If you want to mask data (ie make it visible but aggregate or non-identifiable) then you have to use a different approach.
Hi, I have another question about Dynamic RLS. Do we have to add users under the role permission when publishing the report to power bi online? I thought that the whole point of dynamic RLS is that we dont have to do that as long as users have viewer access to the workspace, otherwise I have to add them two times one as viewer to the workspace
eport and another to the role permission under the dataset security settings. This could be cumbersome when have too many users. Please advise. Thanks
@@BILingualAnalytics Thank you so much for your answer
Hi Samer, i tested from my end, i gave the user 'Viewer Access' to workspace, but it was not worked until i added the same user on the dataset level as well....is workspace viewer access enough or should we also add the security groups on all the individual datasets?
@@kumarprabhu Hi Prabhu, I guess you havd to add them again to every dataset that has RLS implemented on it.
@@samsal073 Hi Samer, Thank you for the reply! That's how i did it at last..Just wanted to know if there is any other alternative approach!...
Thank you for video. When I have tested using PBI desktop did work fine, but when I published on PBI Online (Pro), the system didn´t apply the role, showing me all data. I put and remove my user in the RLS group and didn´t work in both cases. Do you know what is wrong? Thanks in advance.
Just checking, did you feed the UPN (e-mail) of the user when testing online?
@@BILingualAnalytics I have solved all the problems 🙂. Your video helped me a lot. Thank you again.
@@cezarmeriguetti1173 Glad you found it useful!
Here you have multiple tables. What if you have one consolidated table which has all the information like Roles, Geo, Products etc and you need to create one DAX to cater different combinations?
It should work even if the base fields(s) for RLS is in the same table. Just select another field and RLS will add "&&" to the DAX code.
What should be the relationship between dimension and RLS table?
There is no need to have any, that's one of the beauties of this setup :)
Great video about the security setup. I'm struggling to find a working solution where multiple members are provided in the USERPRINCIPALNAME. We can control the format of the "Array" but I cannot decipher it in the Security Role. As an example the USERPRINCIPALNAME can hold for example three Colors Blue#Red#Green. How do I make that into Product[Color] in {"Blue","Red","Green"}
I might not understand the full extent of your question, but I reckon you need to have a row for each detail. But if you can reach out with a bit more detail in an e-mail I can have a better look.
Hi. Yes the video is a great insight into the security setup. Did you get a way to the above question? Am very new in this and am struggling with a similar thing.
@@BILingualAnalytics What was the response on the above dilemma
@@almacharia Haven't heard back with more details
Im new to Power bi , how did you use equal sign to compare Group to CalculatedTable expression in the switch statement . how does this work? you are comparing single value to table
I believe the calculated table is returning a single cell that corresponds to the USERPRINCIPALNAME() row & RLS category column in the Excel security table. Even though it’s still a table, I think that DAX is able to convert the single-cell table to a value when it makes the comparison in the SWITCH statement. Very clever trick, isn’t it!
I suspect that if the Excel RLS table contained two rows for the same user but multiple RLS restrictions for a given category, it would fail the table value comparison in the SWITCH statement.
@@BILingualAnalytics Ah - yes, I see your mention at 14:40 about using the IN operator - that’s an excellent way around the issue. Nice!
Hi,
I am using dynamic RLS but, I have one condition;
If some user have more than 1 region, but at the login time (when user open BI report) can we give option for region which belongs to that particular user? So that, he choose that region & report shows that region data.
Great question! If I understand the details correctly, I would solve that with a button/slicer combo. For those who have access to 1 region, just a single click to "enter" the report. For those who have multiple regions, select the relevant one.
Now, I'm not sure how difficult it is to set up, but in theory would be possible.
Button or slicer is ok but;
Is this possible that 'userID' use as dynamic RLS and at same time region is in static RLS?
If you have any example or solution can you please share with us?
can you please reply ?@@BILingualAnalytics
I have the same model but insted of None i am using ALL. What is happening is that if a user got "All" mention in any column when i run the RLS it is showing blank. If am not using "All" and for those users it is working fine. What may be the reason.
@@BILingualAnalytics I check in both file. both are same. i copy paste the same "All" and use it in the dax. I even change "ALL" to "All".
Please, what happens when a user have more than one access to one specific column? Or, what happens when the same user don't have access to a single category in a column, how do you specify that, please? Could you make a video with these exemples? Would help a lot. Thanks in advance.
If a user is listed multiple times you might need to modify the DAX, see other comments regarding the IN/OR operator.
Great video! I have one additional question.
In my User Table I have multiple rows for one User. A User who should see data from regions France and Germany has two rows. One with the region France and one for region Germany.
For these Users the RLS shows errors. Is there any possibility to solve my problem?
@@BILingualAnalytics Great video, the multiple departments logic also works beautifully!!!. THANK YOU!
@BI-Lingual Analytics - i have the same issue, using an IN statement on the Switch doesn't work, still getting error "a table of multiple values was supplied where a single value was expected"
@@MerlinMathew-tr4if Can you share how you implemented if user has access to multiple regions or multiple departments
Hi my dynamic RLS is not working after fresh data refresh in powerbi desktop and service.. Pls help
Hey, to be able to help I need more info. What has changed? What is the error that you get? Things like that
@@BILingualAnalytics it's like when I have 3 columns which is RLS table connected to fact table with bi directional filtering on region wise and email ids are unique of users, before it was working but after updating new data it's not working, I'm not able to see in view role as well as in service... What it means.. Data is not showing that particular user details it's giving me 0 values but when in data set you hav values.
@@MoonShine-bs6cl any chance you can connect me via e-mail?
configure Row level security using AD groups any recommendations
@@BILingualAnalytics Thanks
Can you please share the PBXI file so that it will help to understand better.
@@BILingualAnalytics the part where am getting stuck is when I use None. If it is any county or product name it will show. But the moment if it is "None" Every thing is coming as blank.
@@BILingualAnalytics I find the issue. It was because of relationship. It's fixed now. Thank you
@@ullassam Hi - what did you do to fix the issue. I am having the same problem with my RLS. None just shows blank
Thank you for this amazing video. Could you please explain how can we handle multiple value here. Lets say one person need 3 store access so in that case how can we handle this three store_id? I have tried many ways but getting error when tried to assign multiple value in _Restriction variable. This is my code
var _restriction = Calculatetable(Values(User_Security[Store_id]),
Filter(All(User_Security),User_Security[Email_id]= userprincipalname()))
var RLS_Restriction =
SWITCH(
TRUE(),
_restriction = Blank(),TRUE(),
[StoreNo] = _restriction, TRUE(),
FALSE()
)
Return
RLS_Restriction
Thank you
Did you manage to resolve this?
Did you manage to resolve it ?
I Missed seeing the data model
@@BILingualAnalytics Can you show the relationships, if any, you established between dimension/fact and your users table?
the background porn music is annoying... drop the music on future videos.
Thanks for the feedback!
your video has good content, but the editing is very bad. Feels like a video game. please stop the 60 fps and the music
Thanks for the feedback!
Hi, im trying to follow your great guide but get the following error message: "The Syntax for 'Users' is incorrect."
var _Restriction =
CALCULATETABLE(
VALUES( RLS Users[Country Code] ),
RLS Users[Email] = USERPRINCIPALNAME ()
)
var RLS_Restriction =
SWITCH(
TRUE(),
_Restriction = "ALL", TRUE(),
RLS Users[Country Code] = _Restriction, TRUE(),
FALSE()
)
Return
RLS_Restriction
Anyone know whats wrong? :)
Never seen such an absolutely crap documentation on any ideas in any software like MS did.. I have no idea how to implement it. It is so counterintuitive. IFs not working, the whole concept od TRUE / FALSE in this area is completely messed up. I see result which are just are completely different from my understanding od Boolean values and DAX after some years of experienced I am 100% stuck in this,
RLS could be difficult to implement. However, with some practice, I would say most of the things that you would like to apply can be done. Especially with Dynamic RLS
This is really a great video on dynamic RLS. 🫶🏽
I used this logic and it’s work great except at one scenario which is quite common to take place.
Considering your same data set let say:
User A has a RLS for All the products in APAC and Only Electronics in EMEA.
Now if I give this data as a input in 2 row of excel RLS table, the output would show All product from APAC and EMEA since there is a “All” in the OR/IN statement from APAC which will overwrite the “Electronics” filter from EMEA.
Is there any way we can fix this?
Thanks!
You are right, it is a more complex setup, but it could still be managed with dynamic RLS.
a) For examples like this you can create a "special" list and use an AND statement. Let's say Geo AND Product should match rather than just Geo or Product.
b) If the whole business is set up like that create the whole user table with that in mind.
Hope this helps