Did you know you can now selectively show report pages for specific users in Power BI?
With the update to the latest version in Power BI Desktop there is now the capability to control which users will see which pages in a report.
This is achieved using a combination of Row Level Security (RLS) and conditional formatting. This makes it a secure way of controlling access for specific users. I will demonstrate how this works below.
The entire dataset does have multiple pages. A home page, a Sales page and an Order page as shown below.
Working Example
Below is a quick overview of what it looks like when implemented. The particular user below is allowed to see any non-financial data such as Order Amount.
What the dataset looks like
Below is a quick overview of how I have configured the dataset. This is essential in order to later configure the RLS to apply the right pages to the right users.
I configured the following tables in Power Query Editor
-
Orders – This table contains all the non-financial order details, along with the date and country
-
Sales – This table contains all the financial sales details, along with the date and country
-
Users – This table contains the distinct list of users which I want to apply the page controls too
-
Pages – This is the pages that I am going to have in my Power BI Report
-
Pages for Users – This table will control which users can see which pages.
- As shown above I would expect the user gilbert@fourmoo.com to see the Home page and the Orders page
Creating the relationships for RLS to work for the specific pages
Below I need to configure the correct relationships which will allow RLS to be applied for specific users.
-
The first relationship is between the tables “Pages for Users” and “Users
- This is where we will later create the RLS filter to control which user can see which pages
-
The second relationship is between the tables “Pages for Users” and “Pages”
- Please see that in the Cross filter direction below it is set to Both and I have also enabled the “Apply security filter in both directions”
- This is for the RLS to be correctly applied when the Page is used in the data model.
- And this is what the relationships look like in the relationship view
Creating the required measures
As with any Power BI Report I needed to create measures for my reports and some conditional measures for my Page Navigation buttons.
-
The first measure is the [Order Amount]
Order Amount = SUM('Orders'[OrderAmount])
-
Next, I needed to create the measures for the Page Navigation.
Page Navigation = SELECTEDVALUE('Pages'[Page Name])
- This measure will get the value from the Slicer which will be configured later.
-
The final measure is used to change the text on the button, making it easier for the user to understand what happens if they click on the button.
Page to Click = "Click to page: " & [Page Navigation]
- This will be shown in the report below
- Just a quick note that yes there is a sales table with measures, I am not going to go into the details in this blog post, but you can download the PBIX at the end of the blog post if you want to see what I did with the sales table.
Creating the Home Report Page
The first page I created was the Home page, my thinking behind the home page is that this is where all users will start and then navigate to the pages that they have access to.
Below are the specific items that I needed to configure to enable the dynamic page navigation
-
I created a Slicer and put in the Page Name from the “Pages” table
-
I went into the Filter pane and de-selected Home, because I did not want the Home page to be an option on the Home page
-
- I created and formatted a button so that it had the look and feel I was after.
-
Then I went into the properties and on Button text I clicked on the conditional formatting button
-
I then selected the measure I created earlier called [Page to Click]
- This will now change the button text when the user changes the slicer.
-
I then scrolled down and expanded Action.
- I then clicked on the conditional formatting for Destionation.
-
I then selected the measure [Page Navigation]
-
- This is what my home page looked like.
Creating the Orders Report Page
Below is how I created the Orders Report page.
- What I did was to copy the Home Page and renamed it to Orders. This allowed me to keep all my slicer and button options.
-
I clicked on the Page Navigation and then on the Filter page and enabled all the items in the list.
- I then renamed the Page name at the top and put in my Orders table.
-
The final step was to hide the Orders page
- NOTE: If I had any other pages that would also all be hidden.
- This is what my Orders Page looked like.
Enabling RLS
Following on I now needed to configure the RLS, this is what allows the specific pages to be shown based on the logged in user.
-
In the ribbon I clicked on Modeling and then selected Manage Roles
-
I clicked on Create and gave the Role the name of Page (Very Original I know)
-
I then went to my Users Table and put in the following syntax
‘Users'[UPN] = UserPrincipalName()
-
- This is what it looked like once I had finished creating the Role
- I then clicked Save
-
To test I clicked on View As from the ribbon
-
I then put in the details as shown below for the View as roles
- Once this was done, I went to my Home Page, clicked on the drop down under Page Selection and my only option was Orders
- I could then only click through to the Orders page and then back to the home page.
-
- I then saved my PBIX and uploaded it to the Power BI Service to a specific App Workspace.
Configuration of the RLS in the Power BI Service
The final piece to configure for the RLS was to put in the same users into the Security option in the Power BI Service.
- In the Power BI Service, I went to my dataset that I had uploaded to my App Workspace.
-
I clicked on the three dots next to my dataset and selected Security
-
I then added my users, as with my example I added gilbert@fourmoo.com
- I then clicked Add and Save
-
I could now see the user under the RLS for my role I created earlier called “Page”
Testing the page navigation
The final step was to test the page navigation.
- I logged into the Power BI Service as gilbert@fourmoo.com
- As you can see below when I opened the report there was no option for the specific pages, just the Home page
- And as previously displayed when I clicked on the Page Selection my ONLY option was Orders.
- And when I was in the Orders page my only option was Home or Orders (The page I am already on)
Summary
This blog post has shown that with the new conditional formatting for the Page Navigation I am now able to control users can only see the report pages I want them to have access to.
I hope that you have found this useful. If you have got any questions, tips or comments please leave them in the section below.
Here is a link to the PBIX I used in this blog post: Page Nav RLS.pbix
NOTE: In the above PBIX I had done some additional configuration where I also limited what the user could see for a particular country.
Thanks for reading.
In this approach, for fully securing, we should disable users taking a copy of report to his personal workspace and also disable build access to dataset, right? Otherwise user can take a copy of report, edit it and see the hidden tabs.
Hi there
If you distribute the report via an App they will still inherit the RLS from the dataset.
As far as I understand it could only happen if they had the ability to download the PBIX, which would then allow them to see the data.
Thanks for the response Gilbert. But the RLS is only securing the Pages table, right? Not the Orders/Sales table?
Hi there
That is correct yes.
If you publish the app the users still have the ability to open the reports from the left reports panel?
Hi Hans, thanks for the question.
In my blog post I hid all the pages, and only had the home page visible.
When this is deployed via an App there is no option to see the report pages in the panel.
Ah yes you’re right. Nice solution!
No worries, glad to help!
If the user export to PDF, the hidden pages are accessible. Thats a fault in that solution for me.
Hi there, thanks for letting me know I was unaware of this.
It is possible to disable the exporting of data in settings though isn’t it? This would be a problem for me except I just want all users to be able to view the reports as opposed to letting them export the info
Hi Brendan,
Yes you can do this by going into the Report settings in the Power BI Service, and then clicking on settings and then under “Export Data” you can select “None” that will disable the exporting of data.
Per our testing, this does not disable export to PPT or PDF which can include hidden tabs. Seems like a security risk.
Hi Andrew,
Yes that is correct, as I said in my blog post this does not lock down the reports as it would with Row Level Security, it just hides the information.
When exporting data all hidden data is available to see.
Hi – Thanks; this really helped to achieve what we want. Reduced a lot of duplicate work.. One more ask instead of List drop-down & clicking button, can we have the page names directly as a button.. I have 6 pages & 6 buttons
Hi there
You could then create 6 buttons give them a name and then create a manual link to each of the pages?
Is there a way for Power BI to automatically redirect based on who is logged in?
E.g. A Financial person mapped to the sales table accesses the Report and is automatically presented the Sales tab. Visa versa applies to the next user assigned to the orders tab.
Hi there
Thanks for the question.
As far as I know there is no way to re-direct them. That is why I started with the Home page, which would then allow them to click through to the page/pages they have access to.
[…] Gilbert Quevauvilliers walks us through access control on Power BI pages: […]
Hello,
Thank you for this. Is there a way it can be developed without using the slicer or a homepage? I have three pages, with two sets of users having two of three pages. I would rather have the 2 pages I have assigned to a username () be visible to those users.
Regards,
Hi there
Thanks for the comment. As it stands today this is not possible due to this being more of a workaround vs how it is built into the product.
RE:this being more of a workaround vs how it is built into the product.
I was just about to respond the same thing. A lot of options and scenarios mainly deal with workarounds when we’re trying to bring extra options.
Some of which would be easily coded in seconds in applications, though dealing with same scenarios in Power BI is a big different ball game!
So in my case I was asked to manage a scenario where some visual components would be hidden to certain users using RLS .rules. Also RLS rules are mainly aimed on datasets and not so much reports contents, such as dealing with visual components visibility unfortunately…
A calculated column named Permission defines a boolean value (either 1 or 0) which I can use in Role setting definitions.
At this stage, I found the main way to manage visual components visibility on a report is through Selection Property and defining Bookmarks.
So in order to achieve this scenario, I would need to pass a dynamic link pointing to a Bookmark in the same fashion as shown.
https://www.linkedin.com/pulse/page-tab-level-security-conditional-navigation-power-bi-a%C5%9F%C4%B1ro%C4%9Flu/?articleId=6663367910938943488
The main issue however is that this can only be achieved Page navigation links and NOT Bookmarks.!!!.. So that leaves me no other choice than making a copy of a report. Sorry but I feel that’s an ugly option …
Is there a simple way to allow everyone to see all pages but only restrict some pages to a few?
If I have 100 users, I would like 95 of them to see all pages and only hide a couple sheets for a the other five.
I would rather not do a cartesian product for all of them.
Hi there
One way is to add the users as members to your App Workspace, this will give them the required full access?
Hi there – this is exactly what I’m looking for but I’m having trouble getting it to work. I’ve pretty much mimicked all the steps above, except it looks like you have an additional measure in your ‘User’ table, UPN ().. what is this measure and where does it go?
I tried to download the file but i’m getting an error with that as well.
Any help would be appreciated. Thanks!
Hi Megan,
Thanks for the comment.
With regards to the ‘User’ table the UPN is something that I manually put into the table in Power Query.
I clicked on the link to download the file and I was able to download the file.
When you open the PBIX and go into Power Query you will see the users table there.
If you still cannot download the file it might be blocked by your IT department. If so please let me know and I can send you the file via email.
Hi Gilbert – unfortunately, the file is blocked due to “enhanced dataset metadata”. If you have the direct file you could send me that would be great! Thank you!
Thank you – I have been looking for this kind of solution for a while. This should reduce a lot of duplicate work for my team
That is awesome, thanks for letting me know.
Thank you! This is something I’ve been wanting to do for a while. I have re-created your solution but when I try to log in as a different user, they can still see everything even though I have them assigned to just certain pages. I’m working in the PowerBI desktop, defined the user role and then published to the service where I added all users to the dataset. Could I follow up with you by E-mail?
Hi there
If you could confirm that the same users DO NOT have any roles in the App Workspace.
If a user has a role other than VIEWER in the App Workspace they will be able to see everything.
If this still does not work, let me know and I will email you.
Thanks for the article Gilbert
Even though transaction table is not secure in above approach and there could be options to see the actual data(Exposing Sales table in edit mode or exporting report to pdf) ,but that approach still gives a way to do more and reach to a secure and personalize reports.
Hi Pravin,
Thanks for the comments and yes it does help personalize the reports for the end users to easily get to the pages they need to get to.
Hi There,
Really great workaround, it’s exactly what I need for our sales division to allow branch managers to only see the ‘pages’ relevant to their own data (as opposed to having to create dozens of what would be essentially the same dashboard, but pre filtered to just their info).
My question is this – as the author of the whole thing, even though I set security so I can only view certain pages myself – just for test purposes – while everything looks to be working as expected in PowerBI Desktop, when I publish it to the App Workspace I still have access to all the pages in the report via the button / slicer on the ‘home page’ of the report.
Is this due to the fact that I created and uploaded the dataset / report? Once it’s in the app workspace my access overrides the security measures created?
Many thanks!
Hi Brendan,
Thanks for the question.
What happens when you are the author of the dataset you will always be able to see everything. That is by design.
Only other people who have the viewer role or read-only access will have their security applied.
Please remember that while this works it is not 100% secure and potentially can be exploited if they know the other pages names.
Hi Gilbert,
Thanks for coming back – totally understand that if a user has a solid knowledge base of PowerBI they may be able to access pages hidden from them … but this definitely won’t be the case in my organisation 🙂 (The data is all in house anyway so if any of the branch managers were really looking to see the actuals of the other branches they’d be able to find the info … if they dug deep enough, which they never do).
Thanks again for this article – the solution is exactly what I need for giving out targets to our branch offices in a single report … will save me a lot of time!
Hi Brendan, happy to help.
And it sure does sound like this will work for you. Thanks for the kind words and let me know if you get stuck again!
Hi Gilbert
Thank you for the great article, however this is not working.
I downloaded the pbix file and when i try to view as other user (eg-richard@fm.com or gilbert@domain.com etc.,), i am still able to choose reports and navigate.
I mimicked the same in my report, and it doesnt seem to be working.
Any help is appreciated.
Kind Regards
Alkesh
Hi Alkesh,
What happens when you test my PBIX version does it work?
If so I would make sure to replicate the exact steps and then see where you are missing something. Without any additional information this is hard to assist.
[…] Did you know you can now selectively show report pages for specific users in Power BI? – Repor… […]
Hello Gilbert.
First of all, thank you very much for the article and your great work. It was exactly what I was looking for and it saved me a lot of time.
My question was partially answered by your response to Brendan McLoughlin.
I replicated the exercise and only allowed myself access to certain pages for test purposes, and it worked perfectly fine in Power BI Desktop. But when I try to see if it works after I published it online, I can still see and access the pages I am not supposed to. I did the same thing for a colleague and asked him to try it out online, and he was also able to access pages he was not supposed to.
Even though I am the author of the dataset, both him and I have the ADMIN role in the workspace. Is that the reason why it does not work for him either? Does it only work for users with the VIEWER role?
Kind regards,
Oscar
Hi Oscar,
You are 100% correct. If a user has any other role other than Viewer they will always be able to see all the data on the reports.
Hi,
Great tutorial, i applied all the steps, however when i select any option in the slicer, the buttom move me to the same page, I reviewed the meassure, but i don’t see any different of what you did.
Hi Agus
If you could please review and check if the button is configured correctly?
WOrking for me!
The above is unfortunately not working for me. I have followed all steps and when I test the permission, the user in question is still able to click to all tables. Any thoughts?
Hi Jaime,
If you can please make sure that the relationships are in place?
Very nice article. I want to highlight an important point that although this approach can help to show report pages for specific users, however, suppose there are bookmarks on the pages, then this solution cannot be used because all user have access to bookmarks pane when viewing the report. Please correct me if I am wrong.
Hi Nash,
Yeah that is 100% correct, while this can work for certain situations there are times when it cannot be done, as with your example.
Great article. Thank you. I am trying to get this to work with a secondary filter. In your example, I want people access to specific page, but then only to a specific country.
I dropped the page rule and developed rule with both Country and Users in it. I can’t seem to get it to work.
Hi Rykie
Yes it does get a bit more complex when you try and add in another country filter. What I would do then is to use RLS to ensure that the user would then only see the countries they need to see.
[…] This blog post shows how I created a Power BI report to only show selected pages based on Row Level Security using the new conditional formatting for page navigation — Read on http://www.fourmoo.com/2020/05/26/did-you-know-you-can-now-selectively-show-report-pages-for-specific-users… […]
Hola, quiero felicitarte por este contenido creado.
Me ayudó un montón.
[…] FourMoo: https://www.fourmoo.com/2020/05/26/did-you-know-you-can-now-selectively-show-report-pages-for-specif… […]
Hi, Gilbert. Nice article. I have a similar question. If I need to restrict one Tab from all users (it is changing everyday) but to give access to specific 4-5 users to see the tab. What would you recommend? Can you please assist? thanks in advance
Hi Azar
Currently the only way to do this would be via the report pages. It is not possible to show or hide tabs at this moment in time.
Hi Gilbert,
Via report pages? Can you please explain in detail?
Hello Gilbert,
Thanks for sharing, this is quire handy. Frankly did not understand the importance of having the table with user names, as the report doesn’t seem to react to the UserPrincipalName() – checked it. It only filters the table based on RLS groups. Could you please elaborate why those steps are needed? Seems like unnecessary complication. However, if that would work as I expect, it could make managing access much easier.
Hi Anastasia, thanks for the comment.
When I was doing my testing this is what was needed to make it work.
What happens if you do not do that step,does it then still work?
Hi,
Can you take this one step further and have any user have access to the entire report but only for the data that pertains to them?
I have a selection page where the user clicks on the contract which in turn filters the entire power bi report.
Could you use this to extend the permissions in this way
Hi Brendan,
Yes you could potentially do this by using Row Level Security to then restrict what the data the users can see.
Hi Gilbert,
Thanks for this solution. I have similar requirement but with little changes. For me I have 4 pages in my report. For my landing page it should be visible to all users with all data. then they click on a button that navigate them to second page and there they should see data only related to them. but with RLS security my all tabs are getting filtered out with specific user data.
Hi Queen, thanks for the question.
What you could then do is to remove the users from the Roles in Row level Security.
Then for each page just filter the page to the users name?
This is genius, thank you! However, I have a similar but different issue. Mine are production numbers for employees. We want employees to be able to see all the stats but only their names will populate on their view.
This measure works for that (I’m using this on the employee page):
Current User =
IF(
SUM(‘Production'[Quantity])
&&
SELECTEDVALUE(‘Employees'[LogInEmail])= USERPRINCIPALNAME(),
SELECTEDVALUE(‘Employees'[Name]))
However, when a supervisor views the report, I want them to see all stats and all names. I used your solution to create two pages one for Employees and one for Supervisors. However, since the supervisor also has numbers in the production table, it only filters the visuals to only show his own production. How do I go about this? Appreciate any help.
Hi Anna,
One option could be to concatenate who the supervisor looks after in terms of users and having all of those users’ part of the selected value so that it would show all the users of the supervisor.