topic Re: Row Level Security with Multiple levels (Complicated) in DAX Commands and Tips

Row Level Security with Multiple levels (Complicated)

Anonymous — Fri, 28 Jan 2022 15:15:30 GMT

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

Re: Row Level Security with Multiple levels (Complicated)

AlexisOlson — Fri, 28 Jan 2022 15:51:50 GMT

Is this the filtering expression you're after?

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

Re: Row Level Security with Multiple levels (Complicated)

mwegener — Fri, 28 Jan 2022 17:07:36 GMT

Hi Anonymous ,

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

Re: Row Level Security with Multiple levels (Complicated)

Anonymous — Sun, 30 Jan 2022 11:54:44 GMT

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

thanks for your time.

Re: Row Level Security with Multiple levels (Complicated)

Anonymous — Sun, 30 Jan 2022 11:55:48 GMT

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.

Re: Row Level Security with Multiple levels (Complicated)

mwegener — Mon, 31 Jan 2022 07:20:34 GMT

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.

Re: Row Level Security with Multiple levels (Complicated)

Anonymous — Mon, 31 Jan 2022 08:16:59 GMT

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

Re: Row Level Security with Multiple levels (Complicated)

mwegener — Mon, 31 Jan 2022 09:05:46 GMT

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

Re: Row Level Security with Multiple levels (Complicated)

Parikrish25 — Wed, 27 Jul 2022 03:59:23 GMT

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