Adding security to a PowerBI report

Create a role

In a report, click on the Modeling tab and select Manage Roles

Click on the Create button.

Enter a name

Select a table, add a DAX formula and click on the Save button.

The role can be tested in the desktop version by selecting the Modeling tab and View as Roles.

Assign a role

When the report is published to the Power BI service, users can be assigned to the role.

In the Power BI service, select the Dataset for the report, click on the ellipse and select Security.

Select the role and add the person’s e-mail address to it. A group or distribution list can also be used.

The role can be tested in the on-line version by selecting the Role, click on the ellipse and Test as Role.

Dynamic row level security

A single role can be used and all users assigned to the role. The security is then derived from a table in the model.

Create a data source listing the user’s e-mail and a key field that they can view, for example country.

Import the data to a table called Security.

Link the Security table to your data. Based on the example above Country in the Security table would be joined to Country in the Data table. Make sure that Apply security filter in both directions is ticked.

Then create a role.

In a report, click on the Modeling tab and select Manage Roles

Click on the Create button.

Enter a name for the role.

Select the Security table, add a DAX formula

[User name] = USERPRINCIPALNAME()

The other option is to use USERNAME() but this returns the domain and user when used from Power BI desktop. When used in the Power BI service, just the user is returned.

Click on the Save button.

Then publish the report and in the Power BI service, users can be assigned to the role.

If you are the owner of the report, you will be able to see all data. Dynamic row level security will be ignored.

If you grant users edit instead of read-only access to the workspace, they will be able to see all data. Dynamic row level security will be ignored.

Users outside your organisation

You are not able to initially add external users to a role under Dataset security.

Until you have shared a report with them and they have opened that report.

The best way to work with this restriction is to create a dummy report with no data.

The report has a single page with some text boxes explaining that they are being registered for security. The page would also have a button linking to a Microsoft form. The Microsoft form has a single question, asking the user to enter their e-mail address. When the form is submitted, you will receive an e-mail.

In the ‘real’ reports, the user can now be added to Dataset security role and the report published to them.