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
Solved! Go to 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 ()
)
Hi @Anonymous ,
you can distribute the rules into own role and then apply them additively to the users.
Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast
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
Hi @Anonymous ,
you can distribute the rules into own role and then apply them additively to the users.
Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast
@mwegener thsi is really great. working out well. could you please explain me how you derive it?
thanks for your time.
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.
Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast
@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
Hi, in this case the function must iterate over the Tasks table, so it is easier to check it over a calculated column.
Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast
Is this the filtering expression you're after?
Tasks[SubmittedBy] = USERPRINCIPALNAME ()
|| Tasks[IsRestricted] = "No"
|| Tasks[RestrictedAccess]
IN CALCULATETABLE (
VALUES ( Groups[GroupName] ),
Groups[Members] = USERPRINCIPALNAME ()
)
@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.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!