Forum Discussion
Row Level Security - Filter Region
Hi,
I have applied RLS to my model which works correctly. Users see their own data. But it gets little complicated when users are in different regions.
For e.g.
User Region Sales
100 A 500
100 B 400
200 C 200
200 D 150
200 E 100
I want to see User 100 only sees Region A
User 200 only sees Region C
How do I add this region piece into the mix?
I tried multiple things:
1. I created a simple table with "User column and corresponding Regions column and joined it to the security table on User but that doesn't work. Still see unwanted regions. Security table doesn't have Region otherwise I could have joined and tested.
2. I joined this simple table with Region table but still see data from another Region when user logs in.
Stumped...
Thanks,
Ritesh
Hi Ritesh_Air ,
Try this:
1. Tables:
2. Create relationships.
3. Manage roles.
For Sales table,
[Region] IN SELECTCOLUMNS ( FILTER ( 'Region', RELATED ( 'User'[UserEmail] ) = USERPRINCIPALNAME () ), "Region", [Region] )For User table.
[UserEmail] = USERPRINCIPALNAME()Then, test.
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Did you create the Roles using DAX in the RLS settings of the desktop? See this article for a full walkthrough of the RLS settings and configuration. Part is done in the desktop, and part in the service after publishing.
Yes. I am doing all these steps. And using USERPRINICPALNAME():
Basically, I want users to see their own Region's numbers, even if they have access to other regions.
For e.g.: RSD 13559 could have access to sales number from Region C1, C2 and E1, but I only want him/her to see sales numbers of C2 because it's their region.
I created this file below and joined region id with region from sales table but as security is on a Person (USERPRINCIPALNAME()), they still see sales numbers from C1 and E1.
Thanks,
Ritesh
Is it a gen 1 workspace? I know the user must not have edit rights or RLS doesn't work. I do now know if that "feature" carried over to Gen 2 workspaces that have more roles (viewer, member, contributor, etc.) but you could set them for Viewer and see if that changes anything.
Hi Ritesh_Air ,
Try this:
1. Tables:
2. Create relationships.
3. Manage roles.
For Sales table,
[Region] IN SELECTCOLUMNS ( FILTER ( 'Region', RELATED ( 'User'[UserEmail] ) = USERPRINCIPALNAME () ), "Region", [Region] )For User table.
[UserEmail] = USERPRINCIPALNAME()Then, test.
BTW, .pbix file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
