Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Row Level Security with Multiple levels (Complicated)

Hi,

 

I am looking to implement RLS for multiple levels.

I have 2 tables as below.

1) Groups

2) Tasks

Groups table will have , Group name and Member email.

And tasks will have below columns

Upon login by the user i want to filter my report in below ways.

Rule: 1) all the records should be visible wherer Isrestricted = NO for every user.
Rule 2: 2) Up on login by User, Rule 1 above should be applied and also, Isrestricted group = Yes and it should look at group table who is the user and how many groups the user is part of and only those groups in Tasks table should be filtered.
Rule 3: 3) Irrespective of above, who ever part of submitted by in the tasks table they have to see all the submitted by records along with Isrestricted access = NO.

 

I have tried relaly very hard to crack this and finally coming here for help from great minds.

 

 

Attaching the PBIX for your perusal. Appriciate your help guys.

 

https://1drv.ms/u/s!ApY7Qk9reuaNiw9L76Qr3dkIwHC6?e=LAe1al


Pragati11 amitchandak ValtteriN Greg_Deckler GilbertQ MikeJohnsonZA AlexisOlson 

Need Help
Need Help DAX
power bi
RLS
RLS problems
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    Is this the filtering expression you're after?

    Tasks[SubmittedBy] = USERPRINCIPALNAME ()
    || Tasks[IsRestricted] = "No"
    || Tasks[RestrictedAccess]
        IN CALCULATETABLE (
            VALUES ( Groups[GroupName] ),
            Groups[Members] = USERPRINCIPALNAME ()
        )

8 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    Is this the filtering expression you're after?

    Tasks[SubmittedBy] = USERPRINCIPALNAME ()
    || Tasks[IsRestricted] = "No"
    || Tasks[RestrictedAccess]
        IN CALCULATETABLE (
            VALUES ( Groups[GroupName] ),
            Groups[Members] = USERPRINCIPALNAME ()
        )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      AlexisOlson really brilliant and you got it with just one rule, i was doing 3 rules all these times.

      but this is really great. thanks for your time.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      mwegener thsi is really great. working out well. could you please explain me how you derive it? 

      thanks for your time.

      • mwegener's avatar
        mwegener
        Most Valuable Professional
        4 years ago

        Hi Anonymous, I think the 3 roles align very well with your 3 rules and the split helps simplify the requirements. Rule 1 and 3 are quite simple as the criteria are already present in the Tasks table. Rule 2 is a typical pattern when assigning groups.

  • Parikrish25's avatar
    Parikrish25
    New Member
    4 years ago

    hi All,

     

    i need to implement row level securtiy for multiple rows. Is it possible Please help.


    Slicers used are three levels

    1. Location

    2.Business Area

    3.Segment / Discipline 

     

    User should see slicer 1,2 & 3 only for his Location for other locations 2&3 slicers should be hidden. Can anyone please help??

     

    For example : Bangalore Location head should only be able to view business area and discipline only for Bangalore & also should see only other Location level data and not the other sub slicers Business area and Discplines