Row Level Security with Multiple levels (Complicated)

[Anonymous]

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 

[AlexisOlson · Accepted solution]

Is this the filtering expression you're after?

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

[Parikrish25]

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 

[mwegener · Accepted solution]

Hi @Anonymous ,

you can distribute the rules into own role and then apply them additively to the users.

[Anonymous]

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

thanks for your time.

[mwegener]

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.

[Anonymous]

@mwegener I agree, Rule 2 is little tricky.

can you please help me understand the DAX we used for Rule 2?

CONTAINS( FILTER(Groups, Groups[Members] = USERPRINCIPALNAME() ), Groups[GroupName] , [RestrictedAccess], the same DAX expression when i add it as a measure it is throwing an error but the same is working in Roles.How do you know that this expression will work in Roles?we usually test the DAX in a measure and then we copy the same to Roles right then how did you know thaqt this will work? please answer me

[mwegener]

Hi, in this case the function must iterate over the Tasks table, so it is easier to check it over a calculated column.

[AlexisOlson · Accepted solution]

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]

@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.