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.
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 email@example.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]1Order Amount = SUM('Orders'[OrderAmount])
Next, I needed to create the measures for the Page Navigation.1Page 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.1Page 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.
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 firstname.lastname@example.org
- 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 email@example.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)
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.