One point that we forgot to mention is that you should add your role filters on a dimension instead of the fact table from a general perspective. I'm sure there may be some circumstances where that isn't true, but in most cases you should add your filters there. Which is what was shown in the video, just forgot to mention that point. By doing it on the dimension, you fix the slicer issue and the filter context will flow to the fact table.
The bidirectional relationship approach you showed, is it only to get the Customer slicer right? And the actual fact data is getting filtered by the DAX filter on Sales Territory dimension? What if I don't have Username on my dimension table and all I have is the bridge table with man to many between sales territories and users? Can I use bi-directional filter option between the bridge table and Sales Territory dimension? In that case, what's the DAX filter I need to apply either to Sales Territory dimension or Internet Sales fact?
Also I do have multiple bridge tables that I cannot combine into one because they are at a different grain. A user can be present in one or more of these bridge tables. In this scenario can we use bi-directional filter approach to filter the data? I used the lookupvalue method and it's very slow.
Thank you for a great video Patrick! In a star scheme model, there is a challange on how to filter multiple dimensional tables (such as Product and Sales Territory in your model). I assume this will be a common challenge for many users. Is it possible to share a video with a step 2 best practise for more Advanced filtering of Product and Sales Territory in your model? Made a solution with multiple SecurityTables that works, but not sure if it is best practise.
Hello Patrick / @Guy in a Cube, Very interesting video, but can you do another video on where to publish a power bi with RLS, especially a workspace provided on MS Teams. I did publish a power bi with RLS on a MS Team workspace, but the people in the team could see all the data because the RLS did not applied. When i tried to publish on "My workspace" then add a tab on the desired Team, it then worked. Can you explain further on this please. This would help a lot. Thank you in advance :)
Thanks for the demo Patrick! Just an FYI for everyone - we have been using the first method for a couple years and its been working great. But we recently produced a large report that was taking a while to render. We used your second method (removing the link between the Bridge table and the dimension table and adding the DAX expression to the Security role) and it sped up the rendering significantly (from 25 sec to like 5 sec)! Lesson being that the plan used by PBI may vary depending on the data and the Role paradigm used.
Hey Patrick Nice video. In my opinion RLS is often an affordable task whenever you have a single fact table. However, once you have a larger model with multiple fact tables - security becomes a totally different size to handle. Especially if all dimensions are not necessarily linked to all fact tables. Could you create / share video or experience on how to handle this? I would really appreciate some inspiration on this. Thx in advance.
Same for me, I have a large data model with 4 different source systems, all merged to one big dataset. Only in one of these darasources there is actually a customer reference. Thanks for your great videos!
Power BI doesn't handle more than 3 tables well. RLS will start hiding rows. SCD2 wont really work on dimensions. Remote DB (Direct Query) will start crawling. I think stacking DAX on top of Power Query on top of a DB has caused a few black box bugs to work around.
How I've tackled this in the past is to shift that LOOKUPVALUE part into an actual column in the Customer table. That way you've got a column that shows who the account manager is, and then you can just apply RLS on that new column. It's quick, performs well, and is super easy to maintain as well!
Hey Patrick, Good Morning. First of all, your channel is a blessing for power bi enthusiasts. i mean honestly at first i thought what the heck is this tool ? why so complex ? but then i took a course on udemy and the instructor gave a shout out to your channel for reference. After that this tool became a piece of cake for me. well not all the topics but the ones that i have understood. Well i am a Tableau Developer and i was also involved in real time work with power bi as well. Normally in terms of tableau, user filter or RLS is pretty easy to implement. Tableau create user filter like a set and we can implement this user filter set as a IN/OUT filter to all worksheets. Done, all the filters are updated with RLS meaning all the filters will now change when we change the RLS values. for example if user1 login then all the filters will show user1 data only. Here RLS implemented on visual or worksheet and to the filters as well. So i was hoping if in power bi, there happens to be something like this apart from bridge and lookup concept ? what i understood is RLS is more like a table level filter which means it doesn't update slicers. but; this question might sound very stupid, as we have a data model set up between dimensions and facts, when we create a role isn't that supposed to filter other columns from other tables present in that model as well.
I have got an issue, if I have Single selection in the filter (in your case customer Full name) then by default power bi selects the first member (in your case Aaron Adams). So When User role Adams Logs in, it shows Aaron Adams + List of customers Adams Role can see. But still Aaron Adams is displayed Because of single selection and he is the default member. As soon as I select any other member in customer filer , Aaron Adams disappears. Please help me how to solve this issue. Note: I need to have Single selection in the filter otherwise I need to change lot of measures.
@@GuyInACube why not stick with the first solution you showed and just change relationship direction to both between Customer and Internet Sales? Also I think even this is not needed as you can add a measure filter on the slicer?
Great video, I work in a car company in Brazil and at this moment, I have a Challenge of converting all the sales report from excel to Power BI, I have 80% of the job done and that is exactly what I need to finish my project, make the reports available by dealer with the security level. This concept of a bridge table, I used a lot, now I know there is a name for it, I use this to create a relationship in tables where the products have different names. Example Table A: Product A 18 Product A 19 Table B: Product A 2 Product A 1 to make this relationship possible, I need first to create groups from the Products names that are Product A and that is it and for the Relationship, a Bridge table with a single source Product A, and the group Product A from Table A and B were related with this Bridge table.
Great video - thanks! I'm keen to see how RLS can be applied across an org hierarchy in the same way. ie. Person A manages Team A so can see all rows for Team A, Person B manage team B so can see all rows for team B. Person C manages Person A and Person B, so can see all rows for Team A and B. I'm looking at a multilevel hierarchy (up to 15 levels), so creating a bridge table listing every team that each person can view might be inefficient. Would be great to hear your thoughts!
Just recently started delving into Power BI and stumbled upon your videos. Just wanted to say how great and helpful you guys are! Thanks and keep up the great work!
Hey Patrick. This video helped me a lot. I've implemented Row level security with the 'lookup' method and extended it with multiple criteria and it works well. Thnx!
The "DAX" method is what we were using for the last 18 years in a solution made on Another BI tool. We do have a process that everynight build the relationship between sales and users and create the table that "said" which record belongs to who. The down side of that is that every sales records MUST belong to somebody. If not, that sales is not going to be shown in the dashboard. So in a sales estructure such as in my company on where we do have sales mans, managers and VP's, we need to ensure the complete estructure is created before a manager or a VP gets into the main dashboard.
Hi Patrick, Very nice video, but where can I find more about your bridge table used in this video. I got a similar problem statement with multiple clients logging in via RLS.
Nice video Patrick, Actually i used the lookup approach because our customer wants the reports in power bi report server , and you know you don't have the role assignment features on-premise . One of our project i used the contains function . But never checked the performance of it . I suggest to make another video for power bi on-premise when you have multiple role for the user . Thanks
Yes , here is a great article from Microsoft for RLS , it should give you a good idea how to implement the RLS with the lookup function . docs.microsoft.com/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular
Patrick! Thanks for problem-solving on this topic. Would you be interested in doing a video on the top 5 technical problems that Power BI users have? Keep up the great work!
Apparently you can only set security on an entire table but not columns. Probably why your tables should be as granular as possible so you can secure an entire table. My opinion
I have not been able to hide columns but you can alter the values of columns based on roles. [Column Name] = 0. I have used this for roles that should not have access to cost and revenue data. I would imagine similar to the lookup you could write a simple if statement and have it show or not show a column based on an if or lookup to your column security table the same as shown in the video.
Thank you so much for this wonderful channel, I'm working with PowerBI Report Server, I have implemented Row-Level-Security and created one role on which every employee shall be assigned. . However, I have over 200 users using the reports. Is there a way to automatically assign every email adress of the users to this single role?
Great video as always! Love the content guys! As RLS only comes with the compromise of loosing some great features like Insights or Q&A have you ever thought about making a video that showcases how you can enable users to only see data within their scope but go beyond that e.g. with a toggle/button? We dont really want to restrict users with RLS but we are forced to do so since reports and dashboards otherwise would simply show too much information. Anyone have a solution? I've been digging for quite a while now but have not found a way to do it. Cheers folks
I am working with a table containing username, Mode and role. There are currently two modes "focus" and "peer group". The roles can be added to a slicer while dynamic rls is set up using userprincipalname(). This should work perfectly fine.
If two different data sources and no common key between them but filters are the same in both of the tables. How could rls do in that situation . Is it work ?
Love your videos Patrick. How can I setup default filters on a dashboard based on the user logged in. Problem: I have managers that login to our quality system where they can see how their departments are performing. We want them to be able to see other departments but when they first login only the departments that they are associated to show.
This is great. But how do I filter my row level security to show data for only two down stages below a manager who is logged into the dashboard to see the report of those below him including his own report. That is, if this manager has four other managers, stage by stage below him in the hierarchy, and he needs to see results for himself and not more than two stages down from him in the hierarchy. How do I achieve this in Power BI?
Thankyou PATRICK, having one more query i.e how do we see the previous days data from the selected date (eg:. if I selected any of the date from the slicer i wanted my bar chart/coloumn chart visual which is having dates on its axis to show 7days previous data from the day I have selected in a slicer)
Hey Patrick, I really enjoyed your video, and I've started to wonder about other case. How would you set up RLS for a report that uses "single select" slicer in its view? I've been trying to set up the RLS and unfortunately once the person who open the report initially and wasn't assigned to that product, he saw a blank page and thought that something might be wrong. Is there a way to avoid it? Thanks!
Hi Patrick, I have a user table and created relationship with Dimension table but the filtering is not happening on fact table. Can you help me resolve this issue?
How would you handle this if your customers could be in more than one region? I'm assuming this only works because you can create a one-to-one relationship between your bridge table and your customer table. But if a customer was in two regions, then that customer ID would show up twice, messing up the join of the bridge table to the customer table. How do you get around that?
Hi, there is 1 measure which we are going to use in 2 visuals in a page. So there is requirement on 1 visual rls implented should work and another visual should not be impacted for eg there is a measure called total sales so first card visual should show total sales based on country selected and 2nd visual should show total sales and rls is implemented based on country so please help me with solution. I have tried through dax, edit interaction. Nothing is working
Hi guys, Thank you for this video which help me a lot to understand this part of security. I wonder is there a way to share with us this Power BI report. I would like to check how you create this model, measures and calculations. Please help me out.
Hi Patrick, nice video and concepts. I have a scenario. I have a set of dashboards and need to restrict the access for specific users. Which means only for a few users I need to restrict but all others I need to allow. Consider I have a table useraccess and it have list of usernames who need restricted access. Can you please help me in filtering this out?
Is there any way to setup RLS for real time dataset? I managed to filter rows based on userprincipalname (as measure), and it works at report level, but when I pin the tile to dashboard, it shows 'zero'....? Thank you.
Hey Patrick, thanks heaps for making these. I learn a lot from your channel. I would have a question about this topic. Is there a way not just to dynamically remove the slicer options using RLS but also to set default option (that is available) for the user? I would like to avoid using the 'All' selection and if I set the default selection myself, it doesnt work for majority of the users as not everyone has access to that particular option and they see a lot of blank/wrong visuals instead.. Have you ever come across this? Thanks!
thanks for the sharing. why you put the false value for the security bridge. I did that with my own dataset and everything is blank. what is the reason to have this step ?
I HAVE A TABLE THAT HAS multiple "many to one " joins on it and when I test RLS on pbi desktop using UPN() it worked on my end. However after publishing the rpeort it has stopped working and users could not see those rows . Any reason why ? can we connect on a call to discuss this ?
Important point: If you've been created on your PowerBI Service workspace as anything other than a Viewer, RLS will not work. You need to be a workspace viewer. This had me stuck for hours!
Hey Patrick, Nice video though however this is my issue. There will be 100 users hitting the report with different level of hierarchy. Regional manager must see performance of managers but managers shouldn't see performance of other manager. There will be around 10 reports,. How to set up RLS once only with these reports?
Hey guys, if you haven't already made a video about it, could you please do one expanding on the bridge table you used at 4:11 ? I tried to find some more information on it but wasn't successful. I'm running into the same (blank) values on a report of mine and I like your fix, I just don't understand that table. If you did already make a video, could you link it for me? Thanks!
Hello Patrick / @Guy in a Cube, Very interesting video, but can you do another video on where to publish a power bi with RLS, especially a workspace provided on MS Teams. I did publish a power bi with RLS on a MS Team workspace, but the people in the team could see all the data because the RLS did not applied. When i tried to publish on "My workspace" then add a tab on the desired Team, it then worked. Can you explain further on this please. This would help a lot. Thank you in advance :)
is there ways to get USERNAME() or Userprincipalname() in Power Query or native custom query before loading data into import or direct query ? like select * from Table1 where email id=Userprincipalname()
I have enterpeise level data and i can filter using the E-mail ID. I used USERPRINCIPALNAME() in the Manage roles and equated to the mail-id but its not working as expected. Is there any other option that i need to enable
Hey Patrick, I Just tried to do RLS with the bridging table, but I got an error when I tried to turn on the "Apply security filter in both directions": "Table 'RLS' is configured for row-level security, introducing constraints on how security filters are specified. The setting for Security Filter Behavior on relationship 'relationship number' cannot be Both." Have you got any ideas where I went wrong?
We are trying to create RLS for AAD groups in our company and not sure how to integrate it whether using office 365 or is there anything we need to do with Azure active directory. The link between AAD and power BI is what I am trying to find out. Thank You
Hey Patrick I have a table which is having user name mapped to the country list. After applying the security i want to see the assigned country for the user in the dropdown as selected with the option of selecting others if required. Can this be achieved? Thanks
At the 2:44 minute mark for testing the row level security, I noticed that for testing you need to check off the box for BOTH other user AND the role - Why is that? Isnt it sufficient to simply select the role?
Selecting just the role will work if you aren't using dynamic security. If you are using dynamic security, you want to specify what to pass in to test the effect. Otherwise it is just your identity.
Hi Patrick, great videos. Can you create measures that bypass RLS without duplicating the data model? In my case, I have a list of vendors, and I want each vendor to see their performance against a total average. I thought about simply creating some measures and sticking them in a table separate from the data model, but that isn't ideal. I would also like the measure to be responsive to filtering. Thanks and keep up the good work!
Hi Patrick, I guess I have the 'in-between' table or what you call it approach. I have a normal user table, with users as corp\useralias. I further have a so-called userauthorization table (that is maintained in an external app) in which all project-subdomains are listed that a user owns (is authorised to). Via the link between the tables userauthorizations and users and the by you explained role security = mid(username(),5,10) on the user table I get only the sub-domains or projects that the user is authorised to see. Anyway big thanks for the video!! Peter
Hi Patrick Great Video. I was working on my data set very similar to what you have, but I have Territories tied to Customers and Territories tied to products. Each user that has access is defined to Territory, and they can see customers and products in that territory only. I did the bridge tables and made the relationship. But one relationship from Productbridge to territory is not happening as it returns the ambiguity error and says I need to delete or deactivate relationship between Sales and TerritoryAccess. If I do that, than I dont get slicing back to sales. Any thoughts on this.
Hi Patrick, I am a BI Professional , Bi is more like a life line to me. I want to pursue power bi professionally for free lancing and providing services Can you please guide me , how should I start and polish my powerbi skills
Hi Patrick, You rock! I have one small query to use this method. Assume I have 2 sheets where in 1st sheet I have 5 region and their data, in 2nd sheet I have 5regions' comments + one overview comments. If filter for region is not selected I need overview comments in my table else I need which ever region is selected comments. Could you pls help me with this. Thanks!
I am having problems with people being able to view information outside of their assigned roles. I have a single table that has transaction information with a department identifier. I have created roles for the specific departments. When I view as role it correctly changes the view to only display that department. I have assigned users to those roles in the Power BI service. When I test this and enter their email to view as that specific user they can view information outside their assigned role. How do I fix this?
I am sorry, but I am new into PowerBI and this is making me a bit confused. So if "ADAM" username log in is in fact "Adam", when he logs in he can only see the graphic that is related to him and cant see anything about Patrick is that it ?
Have you tried this method to control the bookmarks that are available to a user? Can you disable or hide bookmark buttons based on user roles? I have a main menu but do not want all users to have access to all buttons.
Hey Patrick, any advice on how to handle RLS filtering out measures that are being used for aggregating information like a benchmark page showing how a specific country performs against a region without filtering the regions values? Today RLS appears to alter any measures made for aggregation comparison making my benchmark page incorrect. Any help on this would be awesome!!
In my case, I have a data set and two reports, I would like to have different rules, according to the report for the same user. Is it possible to create a new report-based RLS? I think about to get report ID on DAX then apply or no.
Patrick that's great with UserName and UserPrincipalName but what about AD groups? I need to manage RowLevel Security via AD security groups. Can that be done?
Hey Patrick! I'm experiencing something weird with row level security, I've set up my roles and all is good and works, until i put the report in MS Teams as a tab, and them all of my RLS disappears and everybody can see everything. Have you experienced something like that or am i doing something wrong?
Hello Sir... I am working "On-Premises" PWI Report Server. I am using SSAS and created role for RLS. The role is working fine in PBI Desktop but It is not working in the browser. I have "pcname\username" in the username field. What I am doing wrong?
This unfortunately doesn't work for me... If I try to do the [Username] = Username(), it gives me a syntax error because my field has a space ([Assigned Manager] = Assigned Manager()). I've tried using % and _ instead of a space but it doesn't help.
Hi Patrick, We've recently implemented RLS on a report, but I am concerned that all of ours users see a File > Download Report (Preview) option when viewing the report in the service. This allows them to download the pbix and therefore bypass the RLS security and effectively see ALL data in the report. I have read that there is a global admin option to disable downloads, but this would prevent our users from being able to download to Excel which they need to be able to do. Am I missing something here, surely this isn't the case?! Thanks Mark.
You will only see the option to download the report if they have edit rights on the report, or are the owner. From an App workspace perspective, if folks have read only rights (Viewer role with the new workspaces), they will not see the option to download the PBIX.
Hi, thanks for the reply. It seems that this issue is only related to the new Workspaces option. At present all roles (Admin, Member & Contributor) have rights to edit and download the pbix. A new Viewer role is planned, but it is not yet available and so we can't offer read only access to our new workspaces which unfortunately is a really big deal for us. I guess we will just need to wait in anticipation for this new feature, but if you can give any of your MS buddies a friendly nudge it would be greatly appreciated :)
I have a qeustion. How set users in "no rule". For example, I have one security table with my respective usernames, but some administrators does not have any rule in this table. Thus, I want to publish my report and set the rule for just some few users. However, the users that I didn't set any rule, crash their report. In this sense, I created a rule for admin and set them in Power BI Service (Online). Is there another way to do that? (sorry for any english issue - hehehe)
hello I have a problem with RLS , I want to activate the RLS for multiple columns in my fact table , I want to filter the data in all my dimensions , can someone help please ?
Well...what if the "cross filter direction" between "Customer" and "Internet Sales" set to "both"? You said a similar thing with the "Apply security..." option though.
@Guy in a Cube I was thinking the same, why not stick with the first solution you showed and just change relationship direction to both? Also I think even this is not needed as you can add a measure filter on the slicer!
One point that we forgot to mention is that you should add your role filters on a dimension instead of the fact table from a general perspective. I'm sure there may be some circumstances where that isn't true, but in most cases you should add your filters there. Which is what was shown in the video, just forgot to mention that point. By doing it on the dimension, you fix the slicer issue and the filter context will flow to the fact table.
The bidirectional relationship approach you showed, is it only to get the Customer slicer right? And the actual fact data is getting filtered by the DAX filter on Sales Territory dimension? What if I don't have Username on my dimension table and all I have is the bridge table with man to many between sales territories and users? Can I use bi-directional filter option between the bridge table and Sales Territory dimension? In that case, what's the DAX filter I need to apply either to Sales Territory dimension or Internet Sales fact?
Also I do have multiple bridge tables that I cannot combine into one because they are at a different grain. A user can be present in one or more of these bridge tables. In this scenario can we use bi-directional filter approach to filter the data? I used the lookupvalue method and it's very slow.
Thank you for a great video Patrick! In a star scheme model, there is a challange on how to filter multiple dimensional tables (such as Product and Sales Territory in your model). I assume this will be a common challenge for many users. Is it possible to share a video with a step 2 best practise for more Advanced filtering of Product and Sales Territory in your model? Made a solution with multiple SecurityTables that works, but not sure if it is best practise.
Hello Patrick / @Guy in a Cube,
Very interesting video, but can you do another video on where to publish a power bi with RLS, especially a workspace provided on MS Teams. I did publish a power bi with RLS on a MS Team workspace, but the people in the team could see all the data because the RLS did not applied.
When i tried to publish on "My workspace" then add a tab on the desired Team, it then worked.
Can you explain further on this please.
This would help a lot.
Thank you in advance :)
Thanks for the demo Patrick! Just an FYI for everyone - we have been using the first method for a couple years and its been working great. But we recently produced a large report that was taking a while to render. We used your second method (removing the link between the Bridge table and the dimension table and adding the DAX expression to the Security role) and it sped up the rendering significantly (from 25 sec to like 5 sec)! Lesson being that the plan used by PBI may vary depending on the data and the Role paradigm used.
"do a quick Bing search" - nice try Microsoft :D
Lmao. My thoughts exactly.
I wonder how many takes he does on the "Yooooooo!" before he decides on "the one". XD
Hey Patrick
Nice video. In my opinion RLS is often an affordable task whenever you have a single fact table. However, once you have a larger model with multiple fact tables - security becomes a totally different size to handle. Especially if all dimensions are not necessarily linked to all fact tables. Could you create / share video or experience on how to handle this? I would really appreciate some inspiration on this.
Thx in advance.
Hi !
Any updates on this ? I haven't find anything yet
Same for me, I have a large data model with 4 different source systems, all merged to one big dataset. Only in one of these darasources there is actually a customer reference. Thanks for your great videos!
Power BI doesn't handle more than 3 tables well.
RLS will start hiding rows.
SCD2 wont really work on dimensions.
Remote DB (Direct Query) will start crawling.
I think stacking DAX on top of Power Query on top of a DB has caused a few black box bugs to work around.
How I've tackled this in the past is to shift that LOOKUPVALUE part into an actual column in the Customer table. That way you've got a column that shows who the account manager is, and then you can just apply RLS on that new column. It's quick, performs well, and is super easy to maintain as well!
Hey Patrick,
Good Morning. First of all, your channel is a blessing for power bi enthusiasts. i mean honestly at first i thought what the heck is this tool ? why so complex ? but then i took a course on udemy and the instructor gave a shout out to your channel for reference. After that this tool became a piece of cake for me. well not all the topics but the ones that i have understood. Well i am a Tableau Developer and i was also involved in real time work with power bi as well. Normally in terms of tableau, user filter or RLS is pretty easy to implement. Tableau create user filter like a set and we can implement this user filter set as a IN/OUT filter to all worksheets. Done, all the filters are updated with RLS meaning all the filters will now change when we change the RLS values. for example if user1 login then all the filters will show user1 data only. Here RLS implemented on visual or worksheet and to the filters as well.
So i was hoping if in power bi, there happens to be something like this apart from bridge and lookup concept ? what i understood is RLS is more like a table level filter which means it doesn't update slicers. but; this question might sound very stupid, as we have a data model set up between dimensions and facts, when we create a role isn't that supposed to filter other columns from other tables present in that model as well.
I have got an issue, if I have Single selection in the filter (in your case customer Full name) then by default power bi selects the first member (in your case Aaron Adams). So When User role Adams Logs in, it shows Aaron Adams + List of customers Adams Role can see. But still Aaron Adams is displayed Because of single selection and he is the default member. As soon as I select any other member in customer filer , Aaron Adams disappears. Please help me how to solve this issue. Note: I need to have Single selection in the filter otherwise I need to change lot of measures.
Not everyday we see someone so straightforward explaining these great stuffs! Nice work man! Thanks a lot
Glad you liked it. Thanks for watching! 👊
@@GuyInACube why not stick with the first solution you showed and just change relationship direction to both between Customer and Internet Sales? Also I think even this is not needed as you can add a measure filter on the slicer?
Great video, I work in a car company in Brazil and at this moment, I have a Challenge of converting all the sales report from excel to Power BI, I have 80% of the job done and that is exactly what I need to finish my project, make the reports available by dealer with the security level. This concept of a bridge table, I used a lot, now I know there is a name for it, I use this to create a relationship in tables where the products have different names.
Example
Table A: Product A 18
Product A 19
Table B: Product A 2
Product A 1
to make this relationship possible, I need first to create groups from the Products names that are Product A and that is it and for the Relationship, a Bridge table with a single source Product A, and the group Product A from Table A and B were related with this Bridge table.
Thank you Patrick. This was a problem that I was going to have to deal with soon.
Happy to help! Thanks for watching! 👊
Great video - thanks! I'm keen to see how RLS can be applied across an org hierarchy in the same way. ie. Person A manages Team A so can see all rows for Team A, Person B manage team B so can see all rows for team B. Person C manages Person A and Person B, so can see all rows for Team A and B. I'm looking at a multilevel hierarchy (up to 15 levels), so creating a bridge table listing every team that each person can view might be inefficient. Would be great to hear your thoughts!
Just recently started delving into Power BI and stumbled upon your videos. Just wanted to say how great and helpful you guys are! Thanks and keep up the great work!
Thank you so much Sara! Appreciate you watching and learning. 👊
adam is international guy (good sense of humor - patrick)
Hey Patrick. This video helped me a lot. I've implemented Row level security with the 'lookup' method and extended it with multiple criteria and it works well. Thnx!
Woot! Awesome to hear 👊
@Wilfred Kluin
Sir Can you share the code...I am working On-Premises PBI Report Server and the RLS is not working in browser...
Fantastic, Awesome, Amazing and very much interesting.
I LOVE POWER BI
The "DAX" method is what we were using for the last 18 years in a solution made on Another BI tool. We do have a process that everynight build the relationship between sales and users and create the table that "said" which record belongs to who. The down side of that is that every sales records MUST belong to somebody. If not, that sales is not going to be shown in the dashboard. So in a sales estructure such as in my company on where we do have sales mans, managers and VP's, we need to ensure the complete estructure is created before a manager or a VP gets into the main dashboard.
Yup. If you are implementing dynamic security, you will need to account for everything in order for it to show up and for the numbers to be correct.
Thank you for you content and tutorials. And thank you for delivering it in such as user-friendly way.
Hi Patrick,
Very nice video, but where can I find more about your bridge table used in this video. I got a similar problem statement with multiple clients logging in via RLS.
Nice video Patrick,
Actually i used the lookup approach because our customer wants the reports in power bi report server , and you know you don't have the role assignment features on-premise .
One of our project i used the contains function . But never checked the performance of it .
I suggest to make another video for power bi on-premise when you have multiple role for the user .
Thanks
Great point! Will look at circling back with on Power BI Report Server. Of course, if RLS ever comes to PBIRS, it's a moot point 😉
Buddy, do you have an article or project where I can look how RLS is implemented using LOOKUP Function?
Yes , here is a great article from Microsoft for RLS , it should give you a good idea how to implement the RLS with the lookup function .
docs.microsoft.com/en-us/power-bi/desktop-tutorial-row-level-security-onprem-ssas-tabular
Patrick! Thanks for problem-solving on this topic. Would you be interested in doing a video on the top 5 technical problems that Power BI users have? Keep up the great work!
We could definitely do something like that. 👊 Will see if we can pull together some information for that.
Patrick - can you exclude columns based on login I.D. as well so this would be column level security I guess (CLS rather than RLS)
Apparently you can only set security on an entire table but not columns. Probably why your tables should be as granular as possible so you can secure an entire table. My opinion
I have not been able to hide columns but you can alter the values of columns based on roles. [Column Name] = 0. I have used this for roles that should not have access to cost and revenue data. I would imagine similar to the lookup you could write a simple if statement and have it show or not show a column based on an if or lookup to your column security table the same as shown in the video.
Good job, sir!! This is the video I needed! Love it!
Thanks a lot, Patrick. One question, Why did you hide the rows for the bridge table ?
million thanks
Usefull stuff, Patrick I'm a beginner to RLS can you please give a brief overview on publishing report with RLS
Can you please make a video on the Relationship of Cardinality and cross filter of single and both direction
Could you show how you construct this bridge table ?
Top Notch!
Thank you so much for this wonderful channel,
I'm working with PowerBI Report Server, I have implemented Row-Level-Security and created one role on which every employee shall be assigned. . However, I have over 200 users using the reports. Is there a way to automatically assign every email adress of the users to this single role?
Great video as always! Love the content guys!
As RLS only comes with the compromise of loosing some great features like Insights or Q&A have you ever thought about making a video that showcases how you can enable users to only see data within their scope but go beyond that e.g. with a toggle/button? We dont really want to restrict users with RLS but we are forced to do so since reports and dashboards otherwise would simply show too much information. Anyone have a solution? I've been digging for quite a while now but have not found a way to do it.
Cheers folks
I am working with a table containing username, Mode and role. There are currently two modes "focus" and "peer group". The roles can be added to a slicer while dynamic rls is set up using userprincipalname(). This should work perfectly fine.
If two different data sources and no common key between them but filters are the same in both of the tables. How could rls do in that situation . Is it work ?
Love your videos Patrick. How can I setup default filters on a dashboard based on the user logged in. Problem: I have managers that login to our quality system where they can see how their departments are performing. We want them to be able to see other departments but when they first login only the departments that they are associated to show.
This may sound "stupid", but did you guys make a video on bridge tables? Just asking as these are still a bit of a mystery to me. TIA
This is great. But how do I filter my row level security to show data for only two down stages below a manager who is logged into the dashboard to see the report of those below him including his own report. That is, if this manager has four other managers, stage by stage below him in the hierarchy, and he needs to see results for himself and not more than two stages down from him in the hierarchy. How do I achieve this in Power BI?
Thankyou PATRICK, having one more query i.e how do we see the previous days data from the selected date (eg:. if I selected any of the date from the slicer i wanted my bar chart/coloumn chart visual which is having dates on its axis to show 7days previous data from the day I have selected in a slicer)
Hey Patrick, I really enjoyed your video, and I've started to wonder about other case. How would you set up RLS for a report that uses "single select" slicer in its view? I've been trying to set up the RLS and unfortunately once the person who open the report initially and wasn't assigned to that product, he saw a blank page and thought that something might be wrong. Is there a way to avoid it? Thanks!
How does one setup the card looking item that starts as "RLS not activated" then reverts to Username Logged in as at circa 2:50 of the video?
Hi Patrick,
I have a user table and created relationship with Dimension table but the filtering is not happening on fact table. Can you help me resolve this issue?
How would you handle this if your customers could be in more than one region? I'm assuming this only works because you can create a one-to-one relationship between your bridge table and your customer table. But if a customer was in two regions, then that customer ID would show up twice, messing up the join of the bridge table to the customer table. How do you get around that?
Hi, there is 1 measure which we are going to use in 2 visuals in a page. So there is requirement on 1 visual rls implented should work and another visual should not be impacted for eg there is a measure called total sales so first card visual should show total sales based on country selected and 2nd visual should show total sales and rls is implemented based on country so please help me with solution. I have tried through dax, edit interaction. Nothing is working
how would we change the currency symbol according to the country when we have a calculated values in the visual
Hi guys, Thank you for this video which help me a lot to understand this part of security. I wonder is there a way to share with us this Power BI report. I would like to check how you create this model, measures and calculations. Please help me out.
Hi Patrick, nice video and concepts. I have a scenario. I have a set of dashboards and need to restrict the access for specific users. Which means only for a few users I need to restrict but all others I need to allow. Consider I have a table useraccess and it have list of usernames who need restricted access. Can you please help me in filtering this out?
Is there any way to setup RLS for real time dataset? I managed to filter rows based on userprincipalname (as measure), and it works at report level, but when I pin the tile to dashboard, it shows 'zero'....? Thank you.
Hey Patrick, thanks heaps for making these. I learn a lot from your channel. I would have a question about this topic. Is there a way not just to dynamically remove the slicer options using RLS but also to set default option (that is available) for the user? I would like to avoid using the 'All' selection and if I set the default selection myself, it doesnt work for majority of the users as not everyone has access to that particular option and they see a lot of blank/wrong visuals instead.. Have you ever come across this? Thanks!
thanks for the sharing. why you put the false value for the security bridge. I did that with my own dataset and everything is blank. what is the reason to have this step ?
I HAVE A TABLE THAT HAS multiple "many to one " joins on it and when I test RLS on pbi desktop using UPN() it worked on my end. However after publishing the rpeort it has stopped working and users could not see those rows . Any reason why ? can we connect on a call to discuss this ?
Wow thanks Patrick I need to do something just like that for a report...
Important point: If you've been created on your PowerBI Service workspace as anything other than a Viewer, RLS will not work. You need to be a workspace viewer.
This had me stuck for hours!
Hi,great video
And how to pass roles dynamically
Hey Patrick, Nice video though however this is my issue. There will be 100 users hitting the report with different level of hierarchy. Regional manager must see performance of managers but managers shouldn't see performance of other manager. There will be around 10 reports,. How to set up RLS once only with these reports?
Hi is there a way to change quarter to year (columns of calendar table) dynamically? Thanks
I was hoping If you could create a login page where people have access to different pages of a power bi report?
Hey guys, if you haven't already made a video about it, could you please do one expanding on the bridge table you used at 4:11 ? I tried to find some more information on it but wasn't successful. I'm running into the same (blank) values on a report of mine and I like your fix, I just don't understand that table. If you did already make a video, could you link it for me? Thanks!
I'd liked more BOTH direction solution than the LOOKUPVALUE solution because it's seemingly more easy to maintain and performing much better.
Hello Patrick / @Guy in a Cube,
Very interesting video, but can you do another video on where to publish a power bi with RLS, especially a workspace provided on MS Teams. I did publish a power bi with RLS on a MS Team workspace, but the people in the team could see all the data because the RLS did not applied.
When i tried to publish on "My workspace" then add a tab on the desired Team, it then worked.
Can you explain further on this please.
This would help a lot.
Thank you in advance :)
I'm experiencing the same as well!
is there ways to get USERNAME() or Userprincipalname() in Power Query or native custom query before loading data into import or direct query ?
like select * from Table1
where email id=Userprincipalname()
I have enterpeise level data and i can filter using the E-mail ID. I used USERPRINCIPALNAME() in the Manage roles and equated to the mail-id but its not working as expected. Is there any other option that i need to enable
Hey Patrick, I have one issue, how to get group base max value and multiple with our other location by a group.
Hey Patrick,
I Just tried to do RLS with the bridging table, but I got an error when I tried to turn on the "Apply security filter in both directions":
"Table 'RLS' is configured for row-level security, introducing constraints on how security filters are specified. The setting for Security Filter Behavior on relationship 'relationship number' cannot be Both."
Have you got any ideas where I went wrong?
We are trying to create RLS for AAD groups in our company and not sure how to integrate it whether using office 365 or is there anything we need to do with Azure active directory. The link between AAD and power BI is what I am trying to find out. Thank You
Very nice demo !!!!! thank you :-)
Hey Patrick
I have a table which is having user name mapped to the country list. After applying the security i want to see the assigned country for the user in the dropdown as selected with the option of selecting others if required.
Can this be achieved?
Thanks
At the 2:44 minute mark for testing the row level security, I noticed that for testing you need to check off the box for BOTH other user AND the role - Why is that? Isnt it sufficient to simply select the role?
Selecting just the role will work if you aren't using dynamic security. If you are using dynamic security, you want to specify what to pass in to test the effect. Otherwise it is just your identity.
@@GuyInACube Gotcha! Thanks!
Is there any way we can apply row level security using group.. as user does not want to manage users
Hi Patrick, great videos. Can you create measures that bypass RLS without duplicating the data model? In my case, I have a list of vendors, and I want each vendor to see their performance against a total average. I thought about simply creating some measures and sticking them in a table separate from the data model, but that isn't ideal. I would also like the measure to be responsive to filtering. Thanks and keep up the good work!
We have the same issue and I think it is a common scenario in sales. Please do a video on that :-)
Hi Patrick,
I guess I have the 'in-between' table or what you call it approach. I have a normal user table, with users as corp\useralias. I further have a so-called userauthorization table (that is maintained in an external app) in which all project-subdomains are listed that a user owns (is authorised to). Via the link between the tables userauthorizations and users and the by you explained role security = mid(username(),5,10) on the user table I get only the sub-domains or projects that the user is authorised to see.
Anyway big thanks for the video!!
Peter
Hi Patrick Great Video. I was working on my data set very similar to what you have, but I have Territories tied to Customers and Territories tied to products. Each user that has access is defined to Territory, and they can see customers and products in that territory only. I did the bridge tables and made the relationship. But one relationship from Productbridge to territory is not happening as it returns the ambiguity error and says I need to delete or deactivate relationship between Sales and TerritoryAccess. If I do that, than I dont get slicing back to sales. Any thoughts on this.
Hi Patrick,
I am a BI Professional , Bi is more like a life line to me.
I want to pursue power bi professionally for free lancing and providing services
Can you please guide me , how should I start and polish my powerbi skills
Hi Patrick,
You rock!
I have one small query to use this method.
Assume I have 2 sheets where in 1st sheet I have 5 region and their data, in 2nd sheet I have 5regions' comments + one overview comments.
If filter for region is not selected I need overview comments in my table else I need which ever region is selected comments.
Could you pls help me with this.
Thanks!
Right on point Patrick
Thank you Dhawal! And thanks for watching!👊
Nice one Patrick :-) please keep up the great work
Q: what if my fact tables do not have username? I read the pin comment about creating username via dim tables. Do you have an example?
I am having problems with people being able to view information outside of their assigned roles. I have a single table that has transaction information with a department identifier. I have created roles for the specific departments. When I view as role it correctly changes the view to only display that department. I have assigned users to those roles in the Power BI service. When I test this and enter their email to view as that specific user they can view information outside their assigned role. How do I fix this?
Question, i have created a dashboard with multiple tabs and would like to manage who have access to view those Tabs. is there a way to do it?
I am sorry, but I am new into PowerBI and this is making me a bit confused. So if "ADAM" username log in is in fact "Adam", when he logs in he can only see the graphic that is related to him and cant see anything about Patrick is that it ?
Have you tried this method to control the bookmarks that are available to a user? Can you disable or hide bookmark buttons based on user roles? I have a main menu but do not want all users to have access to all buttons.
Hey Patrick, any advice on how to handle RLS filtering out measures that are being used for aggregating information like a benchmark page showing how a specific country performs against a region without filtering the regions values? Today RLS appears to alter any measures made for aggregation comparison making my benchmark page incorrect. Any help on this would be awesome!!
Great video.......stay blessed
In my case, I have a data set and two reports, I would like to have different rules, according to the report for the same user.
Is it possible to create a new report-based RLS? I think about to get report ID on DAX then apply or no.
hi patrick, how to implemen RLS if we connect to SSAS DB? because manage roles button gray out. thanks in advance
Patrick that's great with UserName and UserPrincipalName but what about AD groups? I need to manage RowLevel Security via AD security groups. Can that be done?
Hey patrick how to get that logged in user name? Please tell me.
Hey Patrick! I'm experiencing something weird with row level security, I've set up my roles and all is good and works, until i put the report in MS Teams as a tab, and them all of my RLS disappears and everybody can see everything. Have you experienced something like that or am i doing something wrong?
I am embedding a Power BI report in a sharepoint page, is it possible to do a row level security in which data will change based on the user?
How to pass whole number column values into the IN operator using dax.... Please help
hi Patrick how are you
after doing the RLS I can't publish my report in web so how can I solve this problem??
Hi ,
Is that possible to use RLS with Direct Query mode in power BI Report Server , am using Sql Server as DB .
Beautiful!
Thanks!
Thanks for watching! 👊
Hello Sir...
I am working "On-Premises" PWI Report Server. I am using SSAS and created role for RLS. The role is working fine in PBI Desktop but It is not working in the browser.
I have "pcname\username" in the username field.
What I am doing wrong?
This unfortunately doesn't work for me... If I try to do the [Username] = Username(), it gives me a syntax error because my field has a space ([Assigned Manager] = Assigned Manager()). I've tried using % and _ instead of a space but it doesn't help.
Hi Patrick,
We've recently implemented RLS on a report, but I am concerned that all of ours users see a File > Download Report (Preview) option when viewing the report in the service. This allows them to download the pbix and therefore bypass the RLS security and effectively see ALL data in the report. I have read that there is a global admin option to disable downloads, but this would prevent our users from being able to download to Excel which they need to be able to do. Am I missing something here, surely this isn't the case?! Thanks Mark.
You will only see the option to download the report if they have edit rights on the report, or are the owner. From an App workspace perspective, if folks have read only rights (Viewer role with the new workspaces), they will not see the option to download the PBIX.
Hi, thanks for the reply. It seems that this issue is only related to the new Workspaces option. At present all roles (Admin, Member & Contributor) have rights to edit and download the pbix. A new Viewer role is planned, but it is not yet available and so we can't offer read only access to our new workspaces which unfortunately is a really big deal for us. I guess we will just need to wait in anticipation for this new feature, but if you can give any of your MS buddies a friendly nudge it would be greatly appreciated :)
I have a qeustion. How set users in "no rule". For example, I have one security table with my respective usernames, but some administrators does not have any rule in this table. Thus, I want to publish my report and set the rule for just some few users. However, the users that I didn't set any rule, crash their report.
In this sense, I created a rule for admin and set them in Power BI Service (Online). Is there another way to do that?
(sorry for any english issue - hehehe)
Your image is the GameCube logo from Nintendo? I can't unsee it now 🤔
what is we have a supper user who should have access to all the data while other have to view only their data
hello I have a problem with RLS , I want to activate the RLS for multiple columns in my fact table , I want to filter the data in all my dimensions , can someone help please ?
Well...what if the "cross filter direction" between "Customer" and "Internet Sales" set to "both"? You said a similar thing with the "Apply security..." option though.
@Guy in a Cube I was thinking the same, why not stick with the first solution you showed and just change relationship direction to both? Also I think even this is not needed as you can add a measure filter on the slicer!