Row Level Security - Filter Region

[Ritesh_Air]

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

[Icey · Accepted solution]

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.

[edhans]

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.

[Ritesh_Air]

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

[edhans]

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.

[Ritesh_Air]

@edhans 

Thanks, it is gen 2 and it works perfectly, I just don't know how to restrict users for seeing their own data if it's in different region. I think I need to filter it in the data model but not sure how?

Please let me know, if you need more information. Would be happy to provide.

Thanks,

Ritesh