Creating a dynamic navigation menu with row-level security.
I really enjoyed this feature and it`s one of my favorite things I recently learned to do in PowerBI. I used Row-level security before and it was very helpful when creating a common report for many users. The main constrain is that the reports for each user group look the same - so they need to have the same design and sam visualization, only results are different based on access.
My case was somehow different. There were various business user groups and each of them defined few additional business-specific measures they wanted to see. As a result, pages shall contain different visuals depending on which user will open the page.
Implementing regular RLS and creating one default report will result in one business user group seeing an empty visual created for another business.
The first idea was just to create separate reports for each business with one dataset but :
- Some of the pages on the report were common for all users group
- Some users can access more than one user group
I hate redundancies so what I needed was something like Tab-level security so users can easily navigate through pages only relevant to them.
I manage to set this up with the use of slicer and button as menu and connected Row-level security to adjust slicer content.
Let`s see how to do it step by step:
1. Setup Navigation Table.
The sample report contains 4 tabs and in the demo, the goal is to create two types of users that will be able to see different tabs.
User1 - can see all
User2 - can see just 3 of them
Create New Table:
Inside the new table, we`ll put in all Tabs name and Order column for sorting:
We now need to add 2 measures to the Navigation table:
First will allow as to navigate to selected page
The second is just to show customize text on the button
Now let`s Create another custom table with User Access Management. It will include User group and map group to page name that they can access:
To make it work we need to create a relation between UAM and Navigation tables:
Remember to tick Apply security filter in both directions.
2 Once all is prepared in tables let`s put it to work on the report:
For start create a new slicer visual with Page field from Navigation table as value:
- Make orientation horizontal
- Use single select
Create an empty button
Highlight button you created go to properties drill down Button text and click fx:
Now choose format by Field value and provide the measure Button created in the previous step:
Once you put it on the dashboard it should like this:
Thanks to measure button text will dynamically adjust based on slicer selection
3 Setting up PowerBI service security
The last step is to set up roles in the workspace. First, you need to publish a report in the PowerBI service then go to the security setting of report datasets.
Click on 3 dots next to the data set and choose Security:
On the left side, you`ll have Roles you defined on a desktop so just click on it then add mails or groups of users to assign role and you are good to go
This solution can be easily used to create really useful navigation and User access management system based on report tabs. You can put slicer it in the top section of each report page and use it as the menu, as result different users will see only pages they can access based on their role or assignment. Although I can easily add this to the list of powerBI improvements that should happen
Additionally, it can still be combined with regular Row Level Security so you can actually run both levels in the same which is a really neat feature.
IF you manage to solve this other way I would love to hear it.
Stay tuned with the latest PowerBI tips and subscribe at the bottom or follow me on Twitter.
