Forum Discussion
Helper IPower BI Row Level Security on a single page?
It seems Power BI natively isn't very good with anything other than "all or nothing" RLS, but I'm looking to find a workaround to create a single page RLS in a Power BI tool.
What I have in the tool is a series of pages that look at school Attendance. Most of the pages are school level, but one page shows pupil level data. Is there a way to have the pupil level page be restricted to the relevant school (or even restricted to not show pupils of other schools), but the other pages open to all schools (but still have a school level slicer)?
I was hoping that simply creating 2 new "school name" tables (one called "School Name" & the other "Establishment Name", then linking them to the data & putting the Permissions table on just one to do the job. But that seems too sensible for Power BI to work with.
I wondered if I can get around it by creating a duplicate table of all the data just for the pupil level page & putting the RLS on that?
Anyone know of an alternative workaround?
Hi BassG ,
Power BI doesn't natively support applying Row-Level Security (RLS) to just a single page, as RLS is model-wide by default. However, a practical workaround is to create a duplicate of the pupil-level data table specifically for the page that needs restriction. You can then apply RLS only to that duplicated table by linking it to your Permissions or School table, and use this version exclusively on the pupil-level page.The rest of the report can continue using the original, unrestricted school-level data without being affected by RLS. This allows you to restrict detailed pupil-level data while keeping the other pages open and filterable across all schools.
Alternatively, for a less secure but simpler method, you can create a DAX-based visual-level filter that checks if the user has access to the school and only displays pupil-level visuals when appropriate. From your data model image, it seems you're already working with multiple school-related tables, so it's important to ensure only one of those drives the RLS logic to avoid complications from ambiguous relationships.
5 Replies
- andrewsommer
Super User
You're correct that Power BI's Row-Level Security (RLS) typically applies to the entire dataset, but there are a few possible workarounds to implement "single-page" RLS for your scenario.
If your dataset includes a mapping of users to schools (looks like it does), you can dynamically filter the pupil page while keeping other pages unrestricted.
Create a User Table with School Assignments:
UserEmail | SchoolID -------------------------- [email protected] | A [email protected] | BUse the Following RLS Filter:
[SchoolID] = LOOKUPVALUE(UserTable[SchoolID], UserTable[UserEmail], USERPRINCIPALNAME())Apply this RLS only to the pupil dataset, leaving school-level data unrestricted.
Modify Visuals to Prevent Cross-School Pupil Viewing:
- Apply a filter to the pupil-level table that restricts viewing unless the user belongs to that school.
Please mark this post as solution if it helps you. Appreciate Kudos.
BassGThanks for this.
Unfortunately, the pupil and the school level data all come from the one dataset.
The main columns are School Name, Pupil Name, Pupil ID, Possible Attendance, Present Attendance, & Attendance %.
Each row is for a pupil & for the school overall, it's a calculation of Present Attendance/Possible Attendance.
Good to know how to do it on 2 separate datasets, so I could make a copy of the dataset & use your solution on it.
Cheers, Graeme
rohit1991Hi BassG ,
Power BI doesn't natively support applying Row-Level Security (RLS) to just a single page, as RLS is model-wide by default. However, a practical workaround is to create a duplicate of the pupil-level data table specifically for the page that needs restriction. You can then apply RLS only to that duplicated table by linking it to your Permissions or School table, and use this version exclusively on the pupil-level page.The rest of the report can continue using the original, unrestricted school-level data without being affected by RLS. This allows you to restrict detailed pupil-level data while keeping the other pages open and filterable across all schools.
Alternatively, for a less secure but simpler method, you can create a DAX-based visual-level filter that checks if the user has access to the school and only displays pupil-level visuals when appropriate. From your data model image, it seems you're already working with multiple school-related tables, so it's important to ensure only one of those drives the RLS logic to avoid complications from ambiguous relationships.
- BassG
Thanks Rohit1991, that's exactly what I ended up doing - making a duplicate of the data & pointing everything on the pupil level page to that. Downside of it is there's about 350k rows across 46 columns which increases by about 20k rows 4 times a year.
Cheers,G
Hi BassG,
Thank you for reaching out in Microsoft Community Forum.
Thank you rohit1991 & andrewsommer for the helpful responses.
As suggested by rohit1991, andrewsommer, I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.
Please continue using Microsoft community forum.
Regards,
Pavan.
