Forum Discussion
Use report without access to underlying data?
I've created an HR dashboard with aggregated data on things like demographics and sick leave statistics. This information is 'public' within our organization, and may be accessed by anyone. However, the underlying data sets are exports from our HR systems, and should not be accessible to anyone but HR and associated managers, because they contain sensitive information like salaries and individual sick leave. These data sets are exported to a SharePoint team section accessible to the HR team only.
When trying to access the report, my non-HR-team users get this message:
You cannot see the content of this report because you do not have permissions to the underlying dataset. Please contact the dataset owner to request access.
How can I share the report with all users in such a way that they can access the aggregated data and visuals, without needing access to the underlying datasets? (I'm more than okay with no one being able to drill down into the data.)
Note: I'm fairly new to Power BI, and know next to nothing about rights and permissions.
Hi FlorisMK , Thank you for reaching out to the Microsoft Community Forum.
In Power BI Desktop, load the full HR exports as “Source” queries. Create separate “Aggregated” queries that transform and summarize the data, removing any sensitive fields. Use only these aggregated queries in your visuals. Right-click each source query and uncheck “Enable Load” to keep them out of the data model. Then, under File -> Options -> Report Settings, disable export and copy options to prevent users from accessing data behind the visuals.
Publish the report to a separate Power BI workspace, not your HR team’s. Share it via a Power BI App or give users the Viewer role, this lets them see the report but not access the dataset or source files.
For more information:
Export the data that was used to create a visualization
If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
14 Replies
- Create a new dataset/report that contains only aggregated data (remove all sensitive fields from the data model before publishing).
- Publish this new PBIX file to a different workspace (like a shared or “Read-only” workspace meant for general access).
- In Power BI Service, do NOT give users access to the dataset directly. Instead:
- Share the report using Apps, or Use "Viewer" role in the workspace (Viewers can see the report but not dataset).
- Ensure row-level security (RLS) is not misconfigured—since in your case, you just want everyone to see the same safe data.
- Avoid DirectQuery or Live Connection to protected data sources in the public version.
Hi BhavinVyas3003 , thanks for your quick response. I'm afraid it's a bit too high-level for me though. Most of your bullets evoke a "yes, but how" response in me.
Most importantly, I do not understand how to do your first bullet, and as I read it, it seems to be more a rephrasing of my question than a solution. Yes, I want a report with only the aggregated data accessible to users, but HOW do I achieve that? After all, the source data is required to aggregate the aggregated data.
The source data is direct exports from the HR systems, and I have no control over which attributes are exported. And some sensitive attributes (such as birth dates) are indispensable for creating the demographics.
To be entirely clear, I want to avoid having to edit the source exports, because that would add an inconvenient and time-consuming additional step.
Here's what I'm thinking/hoping would be possible:
- Base the report on the HR exports, which contain all the necessary atomic data --> source queries
- Build reporting queries on the source queries, and measures on the reporting queries, to aggregate the source data
- Use the reporting queries and measures in the visuals of the report
- Make the report and visuals available to my users
- Disallow drilling down to the underlying reporting queries of the visuals
- Disallow accessing the reporting queries directly
- Disallow accessing the source queries directly
The setup is already mostly in place: the report has "Source" queries that read the exports, and reporting queries that Table.Combine the source queries and contain the required measures. None of the "Source" queries are used directly in the visuals.
I guess what I'm also hoping is that it's possible to allow Power BI to read the source exports, without my users needing permission.
Hi FlorisMK , Thank you for reaching out to the Microsoft Community Forum.
In Power BI Desktop, load the full HR exports as “Source” queries. Create separate “Aggregated” queries that transform and summarize the data, removing any sensitive fields. Use only these aggregated queries in your visuals. Right-click each source query and uncheck “Enable Load” to keep them out of the data model. Then, under File -> Options -> Report Settings, disable export and copy options to prevent users from accessing data behind the visuals.
Publish the report to a separate Power BI workspace, not your HR team’s. Share it via a Power BI App or give users the Viewer role, this lets them see the report but not access the dataset or source files.
For more information:
Export the data that was used to create a visualization
If this helped solve the issue, please consider marking it “Accept as Solution” so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
Hi FlorisMK , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.
Very sorry, this got buried under countless higher-prio matters. I hope to dive into this again coming Friday. Rest assured it's not off my radar, as this report is needed by my business.
Hi FlorisMK , Thanks for the update. Please try and check it when you have time and share your thoughts.
Thank you.
Hi FlorisMK , hope you are doing well. can you please confirm, if your issue is solved, If not, please share your thoughts.
Thank you.
Finally! A three-week vacation came between as well, but now I've checked, implemented, and verifed your solution. Excellent!
