The default Power BI template provides an account based security level. This means, that a user only sees data of the service(s) she/he is a member of. Within viewing that data there is no other restriction. The user can see the performance of the whole team.
In this use case, we want to add row-level based security into the report. We want to achieve the following:
- Establish one central account to share the report with all Nimbus Service users.
- Enable row-level security for different roles working with reporting data and assign these roles to Nimbus Service users.
PRECONDITIONS
- One Power BI Pro account is needed to manage the row-level security
- You need Tenant Administrator rights to create O365 users and assign them to Nimbus teams.
Alongside this Use Case we strongly recommend reading this Row-Level security (RLS) with Power BI documentation by Microsoft.
INC Icon Legend Accordion
Show Icon Legend
| 💡 = A hint to signal learnings, improvements or useful information in context. | 🔍 = Info points out essential notes or related page in context. |
| ☝ = Notifies you about fallacies and tricky parts that help avoid problems. | 🤔 = Asks and answers common questions and troubleshooting points. |
| ❌ = Warns you of actions with irreversible / data-destructive consequence. | ✅ = Intructs you to perform a certain (prerequired) action to complete a related step. |
Example User Structure
In our example we work with the following services and users:
User |
Nimbus Service(s) |
Power BI licence |
Role description |
|---|---|---|---|
| Agent1@qnnb.onmicrosoft.com | A - Helpdesk (Enterprise Routing) | none | Agent |
| Supervisor@qnnb.onmicrosoft.com | A - Helpdesk (Enterprise Routing) | none | Supervisor of the Enterprise Routing service |
| Agent2@qnnb.onm | B - Customer Service (Contact Center) | none | Contact Center Agent |
| reporting@qnnb.onmicrosoft.com |
A - Helpdesk (Member) B - Customer Service (Service Supervisor, User Supervisor) |
Pro | Central Reporting Resource Account |
How-To Steps
Create a central reporting resource account
- In the O365 Admin Portal, create a reporting user account.
✅ Note that if you are using Contact Center, this account needs to be licensed for Contact Center. - In the Nimbus Admin Portal, add the account and assign the Nimbus Service Supervisor and Nimbus User Supervisor roles for all OU's to it.
Open the report with the central reporting resource account
- Open the report template Nimbus.pbit and connect to the data with the central reporting account.
🤔 First time connecting? Read our Power BI chapter to learn how to connect users to your Nimbus reporting data. - 💡 Tip: If the report had already been owith opened with another account, you may switch the account:
- Go to Files -> Options and Settings -> Data sources
- Select "Edit Permissions" and Edit the Organizational account.
- Refresh your data source.
✅ Make sure you see the data of all services before you go to the next step.
Create the row-level roles
- Within Power Bi Client, go to Modeling > Manage Roles
- Set the roles according to your needs. Here are some useful role filters:
Role name |
Description |
DAX Filters |
|---|---|---|
| Agent | Should only see statistics for own services and calls where personally involved. | Filter on Table Users: [Upn]=UserPrincipalName() |
| Agent 30 days View | Should only see statistics for own services and calls where personally involved AND only data for the last 30 days |
Filter on Table Users: Users: [Upn]=UserPrincipalName() Filter on Table SessionDates: [Date] in DATESINPERIOD('SessionDates'[Date],MAX([Date]),-1,MONTH) |
| OU Supervisor | Should only see data for USers and Services of a specific OU |
Filter on Table Services: [OrganizationUnitId] = "x000xy0z-xxxx-00d0-yyy0-c0f0000001z1" (use your own OU Id) Filter on Table Services: [OrganizationUnitId] = "x000xy0z-xxxx-00d0-yyy0-c0f0000001z1" |
☝ The DAX expression UserPrincipalName() returns the Azure AD User Principal Name (UPN) of the current user. If you test these roles with the global resource account, you won't see any difference.
💡 You do not need to filter on Services, because the service is already filtered on account level.
- Once done configuring all roles save the report.
Optional: hide the central reporting resource account in the report
If you want to hide the Global Reporting Account from the users, you can use a filter "Transform Data" on the Users Table as described here: Use Case - Filtering the BI Report data model quickly
Share the Power BI report and assign the roles to the users
- Go to https://login.microsoftonline.com/ and sign it with the global reporting account. In our example with reporting@qnnb.onmicrosoft.com
- Share the report as described in this use case Publish to Power BI service.
Show me an excerpt of steps
First - Create a workspace
Second - upload the report file with the added row-level security
Third - Add all Nimbus Service Groups as Viewers to the workspace
- Now manage the roles inside the Data Source.
-
Go to > ... > Security > Row-Level-Security
- Assign the users to the roles:
-
Go to > ... > Security > Row-Level-Security
- In MS Teams, share the report. 💡Note that any user with Power BI Pro licence can share it, too.
