Dynamic Row Level Security

[Tlotly]

Good day 

I need to implement row level security with a Region hierarchy i.e. Region, Zone, Branch and Area. I have 4 regions and each region has a Regional Manager. Each Regional Manager needs access only to their region and the zones, branches and areas belonging to his region.

Example of the Region structure below:

As a test, I'm creating user access at a Region level first. So I've created a User Access table (for Regional Managers) with their respective Regions, email address and Username.

Example of the table structures below:

To test the access, I've used the follwoing DAX on Modelling Tab but it's not working: Please assist

side note: Our users access reports on Power BI Service with their username e.g. domain\Username

Thanks

Matlotlo

[jcalheir]

In the picture you've sent, you are aplying RLS on the UserAcess Table, and i guess your relations beetwen the tables dont allow to to filter RLS all the you to your Center meeting Schedule table.

Try to do the RLS directly on your Center meeting Schedule table.

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[jcalheir]

Hi

I think you probably dont need those 2 mid tables "Region-bridge" and "UserRegion"

You can apply rls directly in your regions table by filtering the Region code field whit this expression:

[Region Code] = LOOKUPVALUE(
UserAcess[Region Code],UserAcess[Email],USERPRINCIPALNAME()
)

USERPRINCIPALNAME() Funcion returns the current user email logged in, so this should work.

Also, this way you dont need a connection between your access table and the region table.

[Tlotly]

Hi @jcalheir  

Thank you so much for the assistance, I really appreciate it.

So I did put the DAX on Centre Meeting Schedule table and it seems to be working.

BUT I'm now faced with 2 issues:

1. When I test on View As, it only works when I've selected  both "Other User" and the role name i.e. Region View. If I test the view as another User and "RegionView" is not selected then it doesn't filter.

2. Also when I view as me, the dataset is blank

Any idea why this is so?

Then when I publish to the Server, the dashboard shows with errors:

[jcalheir]

The first behaviour is normal, you need to select the table your filtering and the option "other user" to select the email you want to test.

Regarding your view, is your email on the UserAcess table?

[Tlotly]

Alright I see, thank you @jcalheir .

For online view, yes my email is on the User Access list but I've also asked another user on the Access list to test on his side and he's seeing the same error message. Is there anything additional perhaps that I need to do on Power BI Service?

Also can I note that users sign in with their NT usernames and not emails. Or maybe I should change USERPRINCIPALNAME () to USERNAME() on Manage roles and see.

[jcalheir]

Well, in that case i sugest that you make two cards, with the measures:

USERPRINCIPALNAME () 

and

USERNAME()

ask your users to login and ask them what they see on both cards.

Then you cand maybe filter your table based on the value it shows to them

[Tlotly]

@jcalheir 

So I have used USERPRINCIPALNAME() and it's working perfectly on desktop version. I think I'm almost there but I'm hitting another brick wall. As soon as I publish to the server and ask the user to test, they see a blank report. 

I've addded the same user as a member under Row level security on the Service and he has "Browser' rights only.

[jcalheir]

Did you tested the cards with the measures  USERPRINCIPALNAME () and USERNAME() what does it show on the service when the user see's the report?

Because that is the value that is going to filter your table trough RLS. And if they are not logged in with their email, maybe you will need to change your filter condition, depending on the value of those functions

[Tlotly]

@jcalheir 

Yes I did test the two. On the Desktop version :

USERNAME() returns domain\username and USERPRINCIPALNAMe returns username@company.co.za

On Published version: They both return username@company.co.za

[jcalheir]

I Guess then you should user USERPRINCIPALNAME for some consistency, but i find it wierd that  you can perform RLS in desktop version and it gives you an error in PBI Service... Not so sure that i can help you further with that...

[Tlotly · Accepted solution]

An update! And sorry I took long to come and update the post.
We have finally managed to implement the RLS, started with the Branch Managers only. Should there be a need to implement for others like Regional Managers, we will do so by adopting the same concept. It's been almost a month now and everything is working perfectly.

Below are the steps undertook to achieve this. This might help someone who might be facing the same issues I had.

We have Centre Details table (which has affinity structures e.g. Region, Zone, Branch etc..) , Centre Repayments (Fact table) and Branch Manager table.  We actually have 2 fact tables but for the purpose of this, I'll use Centre Repayments as an example.

Below is our Affinity structure to give more clarity on how it works: hence we link our Fact and Centre tables on a Centre Code because a Centre is the lowest level.

Our Branch Manager table contains the UPN, as this is what we use to access all the Power BI reports on the Report Server. There was no need to link it to Centre Details as we looked it up using DAX. Below is DAX for that: which we applied to 2 Facts tables and Centre Details. Applying it to Centre Details helped in restricting even the Region, Zone, Branch etc... slicers that we have on the report.

[Branch Code] IN SELECTCOLUMNS(
      FILTER(
BranchUsers,
BranchUsers[UPN] = USERPRINCIPALNAME())
,
"Branch Code"
,[Branch Code]
)

Then for everyone who needs to see everything on the report, I created another role "NoRLS":

Then on the Report Server, we assigned groups to each RLS on the report. You will notice that only Branch Manager group has restricted role assigned to them.

I hope this is clear and helpful.

Thank you

[jcalheir]

Thats a pretty interesting solution, very clean as well, thanks for the share.

Glad i could help 😀

[Tlotly]

@jcalheir  Thank you 😊

[Tlotly]

I really appreciate your help and patience so far @jcalheir . I'll keep on checking and will advise once sorted.

[Tlotly]

Alright I will try it and advise when I get sorted. Thank you very much!

[jcalheir]

Glad i could help 😀

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[Tlotly]

Hi @jcalheir 

Thank you for the quick response.

This DAX works if I test it as a measure, see below: I've added it as measure on UserAccess table and tested it on a card

Measure = LOOKUPVALUE(UserAccess[Region Code],UserAccess[Email],USERPRINCIPALNAME())

Results on a card:

But as soon as I apply the same on the Modeling tab, it does not filter results to show only this Region Code.

The results need to be filtered on the Centre Meeting Schedule dataset but it's not working:

I'm not sure what I'm missing here. I deleted the relationshop between UserAccess table and Centre Meeting Schedule table.

Please assist.

Thanks