Update: Power BI now has native functionality for this scenario, and the workaround approaches described below are no longer needed. For additional information, see this blog post from March 2023.
Have you ever needed to build a Power BI application that make insights and data available for use used by multiple groups of users, where those user groups have some overlapping needs, but also some exclusive needs?
Maybe requirements like these?[1]
- A dataset with sales data that needs to be used by store managers and district managers
- A sales report that contains both store-level and district-level pages
- Each district manager should see only data from stores in their districts
- Each store manager should see only data from their store
- District managers should see all pages
- Store managers should see only store-level pages
Until we got to the last two requirements, you were probably thinking about row-level security, but then you may have started to have doubts. Hold that thought.
First and foremost, protecting data at the source is the most reliable way to prevent unauthorized users from accessing what they are not entitled to see. Hiding elements in the UI layer is not a security mechanism and should never be treated as one.
Let’s make that stand out a little more with some fancy formatting.
Protecting data at the source is the most reliable way to prevent unauthorized users from accessing what they are not entitled to see. Hiding elements in the UI layer is not a security mechanism and should never be treated as one.
Sometimes security isn’t everything you need – sometimes you need a targeted or adaptive user experience as well. This just got a little easier with Power BI and some recently released capabilities for conditional formatting.
Here’s the short version:
Using some of the same techniques you use for row-level security, you define roles in your data model that say what users can see what pages. You hide the pages in your report, and you implement navigation controls in your report using buttons on report page surface. When you publish the report, you define row-level security rules in the report dataset to identify what users and groups belong to which roles, which in turn are used for the conditional formatting used to control report navigation.
Here’s the long version: Read these blog posts from Gilbert Quevauvilliers and Marc Lelijveld.
- https://www.fourmoo.com/2020/05/26/did-you-know-you-can-now-selectively-show-report-pages-for-specific-users-in-power-bi/
- https://data-marc.com/2020/05/22/personalize-your-power-bi-page-navigation/
Marc and Gilbert both go through step-by-step detail and each one explains the approach slightly differently, so I recommend reading each of their posts. They’ve saved me the trouble of writing up something detailed myself.
This approach gives you another tool in your Power BI tool belt, and may be useful if you’re trying to reduce the number of reports you manage and want to customize a single common report for multiple audiences.
[1] This is a generic example with generic data and roles, but the actual scenario details seem to be common across industries and data domains. I’ve heard variations on this theme from enough customers to believe that this is a common use case.