Appreciate this video, this info is hard to find. I read that if you're using dataverse and set directquery in the datasource, the row level security is automatically accounted for. I assume performance won't be so great. just another tidbit of info.
Yeah, I’ve seen that but haven’t tried it - I’ll have to check the performance sometime. The role info is in a Dataverse table too, so you might be able to mimic it with import mode if you got creative with it.
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.
Thanks for the feedback, I am learning as I go! It should be better in the newer videos, I've been doing 300% UI zoom and doubled the recording resolution.
Sure, here you go: github.com/chpayton/BI-Files/blob/main/Misc/AdventureWorks%20-%20RLS.pbix I just did a video today on how to get this same database into a free SQL server and connect in PBI if that's helpful - it's in my recent video feed
This is a wonderful resource-thank you! However, with this solution, it seems users are able to see data of those above them as well-so you have a solution so that users only see data of people in their down line?
It shouldn’t if it’s set up right. If you take the CEO as an example, the person who reports to the CEO looking at a report will will not be able to see the CEO’s rows, because the CEO’s path does not contain that user’s UPN - the pathcontains function would return false for those rows. The people reporting up under that person do have that person in their path, so the manager would see their rows.
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--
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.
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.
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.
Perfect timing on my end. Now that our data is somewhat usable we are finally creating reports which need RLS. Thank you :)
Took me three days of truoble-shooting until i found your video and got the solution I was looking for. You are a life-saver Christine ❤❤
I think I am your new BIGGEST FAN! Thank you so much for these walk thrus!
This is THE best video on Power BI RLS! Thank you!
thanks! 😄
This must be the first video I've seen that has helped me understand RLS. Thank you so much :)
Glad it helped!
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. :)
Appreciate this video, this info is hard to find. I read that if you're using dataverse and set directquery in the datasource, the row level security is automatically accounted for. I assume performance won't be so great. just another tidbit of info.
Yeah, I’ve seen that but haven’t tried it - I’ll have to check the performance sometime. The role info is in a Dataverse table too, so you might be able to mimic it with import mode if you got creative with it.
Simply superb 🎉🎉🎉🎉🎉🎉🎉🎉🎉🎉🎉
Thank you so much, this video tip help me a lot!
Yu explained very clear thank you!
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.
Does this allow second level managers to see all the people that align through them (in their downline), not just their direct reports?
Yep! That's what PATHCONTAINS() does for us as opposed to just referencing the manager UPN.
I'm the 100th person to like the video. Very Informative. Can't be better than this.
The resolution of your monitor is much higher than your recording resolution, which creates distorted text for viewer.
Thanks for the feedback, I am learning as I go! It should be better in the newer videos, I've been doing 300% UI zoom and doubled the recording resolution.
Very helpful video.
Just a request can you please share the pbix file used in the video
for better understanding
or just data
Sure, here you go: github.com/chpayton/BI-Files/blob/main/Misc/AdventureWorks%20-%20RLS.pbix
I just did a video today on how to get this same database into a free SQL server and connect in PBI if that's helpful - it's in my recent video feed
This is a wonderful resource-thank you!
However, with this solution, it seems users are able to see data of those above them as well-so you have a solution so that users only see data of people in their down line?
It shouldn’t if it’s set up right. If you take the CEO as an example, the person who reports to the CEO looking at a report will will not be able to see the CEO’s rows, because the CEO’s path does not contain that user’s UPN - the pathcontains function would return false for those rows. The people reporting up under that person do have that person in their path, so the manager would see their rows.
@@bi-ome thank you! :) I got it sorted
So glad I found you.
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--
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.
Pretty AWESOME, thank you!
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.
Thank you that's great stuff
life saver!
Nothing is visible in ur video