Forum Discussion
DAX Code for Dynamic RLS
sorry, was a bit quick there, you need to write it like this
VAR _t = CALCULATETABLE ( VALUES ( 'Access_Table'[Access_Required] ), FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () ) ) RETURN IF ( CONTAINS ( _t, 'Access Table'[Access_Required], "National" ) || CONTAINS ( _t, 'Access Table'[Access_Required], "Regional" ), 'dimCompany'[Company] IN CALCULATETABLE ( VALUES ( 'Access_Table'[Company] ), FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () ) ), TRUE () )
Thanks for your reply!
I've uploaded a dummy report here: https://1drv.ms/u/s!AryWZk9AIwc0gWDGkXIsaerDX5Y3?e=wlq4Mh
Resident RockstarThe sample you provided is a bit short on data, so it is not really suited to see the full effect of RLS. But this how I would set it up
1. Change up the model to look more like a star model:
2. Click on the manage roles-button, and create a role called e.g. all. Then on the tables which are prefixed with dim, add the dax statement which will filter the table according to the access level they have according to Access Table:
3. You can then test your role like this, add the e-mail of the user you want to test for, and click 'OK'
When you publish it to power bi services, you need to go to the Dataset section in the left menu, and for the dataset of your report, click the three dots and find Security. You should have a ad/security group called something like Everyone or All, add this group to the role you have created.
Firstly, thank you so much you've been really helpful!
I am 99% of the way there however I am having issues with the 'Regional' filter. Where currently if you are under the regional filter you can see your region e.g. USA but for all companies and can filter through other companies, instead of being able to only view your companies region.- sturlaws
Sorry, you lost me. You want to see region but not all companies in that region? And only see your own company?
Could you create a mockup to show your desired outcome?
So each company could have a number of regions under it for example:
Company Region Apple UK Apple USA Apple CAN Microsoft UK Microsoft USA Microsoft CAN When a user is set up as 'Regional' it means that they should only see records that are in their region AND in their company as this will be shared externally. Does that make sense?
sturlaws wrote:Sorry, you lost me. You want to see region but not all companies in that region? And only see your own company?
Could you create a mockup to show your desired outcome?
Hi, sorry to be bringing this post back to life.
When publishing the report to PBI Service I understand you have said "You should have a ad/security group called something like Everyone or All, add this group to the role you have created."
Can you provide further information on what you mean by this and what needs to be done? Currently I can see the ALL option but not sure what email I meant to add to that list?- sturlaws
Are you asking about how to add users and groups to a role? Or why you should use All/Everyone?
So, would I have to add every user that is in the access table to this group? The idea was that this report would be shared externally and then the external companies would be updating a file that would populate the access table. Which I was hoping would mean I wouldnt have to add 100's of users to the report. Is this not the case?