Forum Discussion

Ritesh_Air's avatar
Ritesh_Air
Post Patron
6 years ago
Solved

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    ...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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's avatar
edhans
Community Champion
6 years ago

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's avatar
Ritesh_Air
Post Patron
6 years ago

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's avatar
    edhans
    Community Champion
    6 years ago

    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's avatar
      Ritesh_Air
      Post Patron
      6 years ago

      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