Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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.

johnbasha33_0-1643372655475.png

And tasks will have below columns

johnbasha33_1-1643372680433.png

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 

2 ACCEPTED SOLUTIONS
AlexisOlson
Super User
Super User

Is this the filtering expression you're after?

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

View solution in original post

mwegener
Most Valuable Professional
Most Valuable Professional

Hi @Anonymous ,

 

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

 

04_RoleView.png

03_Group.png

02_SubmittedBy.png

01_NotRestricted.png

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


View solution in original post

8 REPLIES 8
Parikrish25
New Member

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
Most Valuable Professional
Most Valuable Professional

Hi @Anonymous ,

 

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

 

04_RoleView.png

03_Group.png

02_SubmittedBy.png

01_NotRestricted.png

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


Anonymous
Not applicable

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

thanks for your time.

mwegener
Most Valuable Professional
Most Valuable Professional

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


Anonymous
Not applicable

@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
Most Valuable Professional
Most Valuable Professional

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

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


AlexisOlson
Super User
Super User

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
Not applicable

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.