Innofactor's deep-dive blogs provide in-depth insights into various Microsoft technologies, including Azure, Dynamics, Power BI, and Data Platforms. Written by our talented professionals, these articles are designed to help you discover new tips and tricks.
Our target is to make one report that has different pages to users depending on which AD groups they belong to.
Our requirement is simple and basic, but Power BI doesn’t support this type of solutions, so the solution described here is quite complex.
But before going to our solution, let’s consider first what mechanisms there are available for doing this. Here is the list from worst to best:
1. Using graph functionality
With URL https://graph.microsoft.com/v1.0/groups/{YourAdSecurityGroup}/members, it is possible to query members of an AD group.
This solution has a few weak points:
2. There is an integration to AD in beta phase (2021-06) which perhaps fixes first one of those issues. But the second issue remains,
3. Using row-level based security
Most of security experts say that only row level-based security is a safe mechanism to implement differing access permissions to different groups.
But it has its weaknesses:
4. Two applications
Simplest solution is to write a separate report for every role.
This has its drawbacks as well:
5. Combining button Page navigation programming feature with role-based security
These two should really have nothing to do with each other, but with the lack of options, we will do this exactly.
Here are instructions that we used to implement this: https://www.solution-tailor.com/post/powerbi-tab-level-security
Since this solution only changes buttons, you could assume that it is not safe: if you send link to private pages to person who has access to public pages, there is nothing to prevent them seeing the private content. But Microsoft has done good work in here: I don’t know how they did it, but this solution is safe, that scenario didn’t work when we tested it on our home page solution.
Reason why there are so many limitations to this technology are coming from the security side.
You need to design your report to have either
With either case, you need to use page navigation. This solution doesn’t work for bookmarks (2021-06).
1) Rename your pages to official page names you will later on have as buttons.
2) Write your page names to an Excel:
3) Create duplicates of the pages you need to have two versions (Sales) and do the needed changes.
4) Open Power Query Editor in Power BI Desktop, select New Source -- > Excel -- > Select the File -- > Open -- > Select Table1 -- > OK
5) Rename Table1 as RolePermissions.
6) Close & Apply
7) Create roles “Private” and “Public” with DAX Studio or from menu Modelling -- > Manage roles.
8) Write DAX code [Role] = "Private" and [Role] = "Public" for table RolePermissions.
9) Save and Publish your project.
10) Go to Azure or Power BI service and select three dots for the dataset:
11) Select Security (don’t mix Security and Manage permissions with each other).
12) Add users and/or groups to both roles. If user doesn’t have permissions to either role, then he will get an error.
13) Create your buttons. There are two alternatives for that. Hard-coded buttons:
a. Create page buttons on home page or on every page.
We opted to show all buttons to both roles.
Shared buttons point to different pages depending on the role.
Then for buttons that are only to one of the groups, you can program them to give an error by pointing them to non-existing page, such as “Foobar”. Or you can program them to do nothing, by leaving the page name empty for the role as I did on this example.
b. Enable Action, set Type as Page navigation and make a function on Destination which points to the correct page (here Budget and costs):
c. The summarization value affects only on admins and people who are added to both/all roles, so decide if they should get first or last value.
d. Create Home or Back button on each page. I recommend for you to hardcode it to the home page.
14) OR create automated two-button solution. So the first button will only change where the second button will be linked to.
a. Make a second sheet to our Excel with columns Page and Order.
b. Copy-paste page names from both private and public rows using Transpose.
c. Add sorting numbers to the column Order.
d. (Optional: Add an empty page with sorting number 999. This is needed on rarer cases because when you program this, you need to set one page as the default selection.)
e. Create a new import for the table 2 and rename it as Navigation.
f. Filter out empty rows.
We had extra pages, bookmarks, and decided to add these to the Excel, but without sorting number, and then to filter these out at this point.
g. Write measures for the button text and button actions:
h. Button text:
Button text = "Click to go to " & [Page navigation]
i. Button action:
Page navigation = SELECTEDVALUE(Navigation[Page])
j. Create somewhere the only functional button. Set Button text as its text, and as the tooltip.
k. Set button action as Page navigation with this destination:
l. Create slicer and set Page as its field:
m. Change slicer as single select and horizontal to make the list to look like buttons.
n. Set relationship between roles of RolePermissions and Navigation:
o. Now when you press from the slider, it does nothing except changes the second button text. And you need to press the second button again.
p. Administrators will see all buttons. You can assign testers to both roles as well. And then rest of the users will see either public or private buttons.
15) Test
a. Press F5 to refresh the page!
16) You can verify the security situation by sending an URLs to private page and to shared page to a person with public access. Admins can’t test this.