Dynamic Row-Level Security in Power BI
Вставка
- Опубліковано 22 лип 2024
- If you are looking to automate your row-level security (RLS) or apply it to your entire organization, using the management reporting chain is a great option. This will let your leadership see data for anyone under them in the reporting hierarchy without having to add individual users to different roles! We use PATH() and PATHCONTAINS() here to achieve this result.
If you do not have a table of user data, which this technique requires, you can get it with Power Automate using this video (filter the resulting data on "UPN does not contain #EXT" to remove external users):
• Get ALL User Profile D...
0:00 Intro/demo
2:53 Relate staff table to data
3:28 Create a PATH() calculated column
4:30 Create the RLS rule
5:30 Test the rule
5:55 Add to role in web service
7:30 Test as role in web service - Наука та технологія
This must be the first video I've seen that has helped me understand RLS. Thank you so much :)
Glad it helped!
This is THE best video on Power BI RLS! Thank you!
thanks! 😄
So glad I found you.
Perfect timing on my end. Now that our data is somewhat usable we are finally creating reports which need RLS. Thank you :)
I think I am your new BIGGEST FAN! Thank you so much for these walk thrus!
Really good video! Thanks for sharing!
Thanks!!
Very useful, as always 🙂
Thanks!! I've been stoked to get to this one for a while, glad to finally get it out there. :)
Pretty AWESOME, thank you!
Simply superb 🎉🎉🎉🎉🎉🎉🎉🎉🎉🎉🎉
life saver!
I'm the 100th person to like the video. Very Informative. Can't be better than this.
Thanks for sharing. In a case where an email can access 2 or more sales orders?
Not sure what you mean - that sounds like a perfectly normal case to me, usually you'd apply the RLS to the "owner" (or similar field), where they would be able to see any rows they are listed as an owner of, their manager can see those same rows (and the rows of others that report to them) and so on.
Great Video, thanks for sharing. How could this approach be extended to the region, district, or branch access view?
Thanks, that's a great question. It depends if it needs to be hierarchical, or if you just need region members to see rows for their region as an example. The key to all this dynamic filtering stuff is to have a table that holds the users and a column for what you're wanting to filter on. So if you're doing by region, you'd have a table with your employees, their region ID, and then relate that back to your primary data table on the region ID. Then you can apply the RLS to the employee table (making sure the filtering direction goes towards the fact table) on USERPRINCIPALNAME() and they'll see rows for their region (don't forget to add "everyone" to the role in the web).
If you're wanting to filter on the hierarchy of those things, you need data that holds that hierarchy to use for PATH() and PATHCONTAINS(), which is usually the tricky thing to get a hold of - that's why it's easier to go via people "reports to" because that information is usually readily available. :)
@bi-ome for example, if a General manager logs in, they would see all the District that roll up the region. A manager would see all the branches that roll up the District. Etc.
Great and simple!
Question, I guess it is quite advanced, but do you have any link or idea how to achieve the same but taking in account that my data model is made from direct query source, where RLS according to my knowledge and googling, cant be applied. Test as user doesn't function.
Do you have an idea how to do that?
RLS on a direct query table has to be super basic - it won't let you use the more complex functions. What you could do instead though is try taking your filtering logic, putting that in an import table, and then relating it to the DQ table, assuming that the RLS rules don't have to be direct query--
Loved the videos but you may want to zoom in when youre writing dax or pointing to sumn because its so small i can barely see it
Thanks yeah - I have started doing resolution zoom on all the new videos. Can’t fix the old ones retroactively :/
5:38- What if we do the DAX, set up the role and then each user sees nothing?
Edit- figured out that when you go in to view as other user, you have to enter in their e-mail address, not their name.
If you've published it, you need to add users to the role in the web. If you're doing this with a large group of people, usually there's an Active Directory group hanging around that has all the people in it, so you can add that instead of having to do it for each person. You can "test as" a person to see what they would see in desktop, so make sure that works first before publishing.
How to deal if one person has 2 or more managers ?
I've never heard of someone having more than one direct manager in a hierarchy, but if that's your case you don't have to use PATHCONTAINS(). If you can get the usernames of everyone you want considered concatenated in a field, you could just use CONTAINS() on that field instead.