Dear Experts,
I am working on department Sickness report where I have 2 pages.
First page is overview where All 3 managers of the department will see basic stats of the department sickness.
Second Page I want to create for each manager view using RLS. Where then can see only the stats related to staff member reporting to them only.
When I apply RLS roles and view those roles as 1 of the manager it is filtering both overview and manager view as well. Which I do not want. I only want Manager view to show the stats reported to staff member reporting to that manager, overview page should remain unchanged and show stats for department wise.
Can you please guide how can i achieve this.
Thank you for your guidance.
As mentioned in another thread yesterday, to help the forum members further analyze your current state and visualize your issue, could you please provide as many as you can of:
• Your work-in-progress PBIX file, using sanitized data if necessary
here’s @BrianJ’s YouTube video on how to anonymize data in a Power BI file: https://www.youtube.com/watch?v=VmWD7Ayw_NI
• Your dataset as an Excel file (again, sanitized if necessary)
• A detailed mock-up (marked-up screenshot of the visual in question or Excel file) showing your desired outcome.
Also, if you provide DAX or Power Query code in your post, please format it using the built-in formatter.
Hi @Greg,
Thank you for the reply. Unfortunately i cannot share the data as the office laptop does not allow and due to confidentiality. I will try to make sample data models and pbix file and upload soon.
Thank you.
@AntrikshSharma beat me to the same reply that I was in the process of submitting:
Create and publish two separate reports. The first report would be a summary for all report viewers. The second, more detailed report applies Row Level Security to accommodate specific report viewers. This avoids the temptation and pitfalls of reliance upon obfuscation for security at the page level.
These are my top six Guardrails that I send out to folks who submit a
customized report request like this:
Row Level Security is applied to and secures the source data.
Object Level Security is applied to and secures the names and metadata of the source data.
Object Level Security is applied to Viewers in a workspace, not typically in the Power BI Desktop file.
Workspace members assigned Admin, Member, or Contributor have edit permission for the dataset, and, therefore, OLS doesn’t apply to them.
“Page Level Security” is simply Obfuscation.
Page Visuals are not managed by Object Level Security.
Just out of interest, if you created an aggregated table, in Power Query during the ETL that was used purely for the summary, and had no relationships and was added to a table or matrix showing the aggregated details in a visual, would that survive the RLS?
That said it depends on the summary page in question and what is on there.
@kylie.oconnell The table will be excluded from Security, as the name suggest Row Level Security works row by row, the DAX filter that you write in a role is applied in the row context of the table plus it also propagates using relationships, either you need the filter on the aggregated table or relationship for a table to be secured.
Thanks for feedback and explanation, so instead of having 2 reports, if the summary was a simple sum or average, using a pre aggregated table with no relationships would show the same for both managers, and the details page would show only what each manager has access to using RLS.
There is always the opportunity to design Partial RLS for use in a Summary report; however, my experience is that Partial RLS is governed by the organizational defense-in-depth security posture rather than a design opportunity.
