BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
To prevent the wrong people seeing the wrong data in your reports, you can create row-level security (for example, so only the London regional manager can see London data). This blog explains how!
- Securing Power BI Reports and Dashboards
- Row-level security in Power BI Desktop (this blog)
- Row-level security in Power BI Service
- Who can see what in your reports
Posted by Andy Brown on 08 July 2017
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Row-level security in Power BI Desktop
Before you read any further, know this:
You used not to be able to use row-level security in Power BI unless you (and those viewing your reports) had Power BI Pro. However, Microsoft have now removed this restriction.
With that out of the way, let's look at how to stop those pesky Southerners seeing our good Northern data!
How row-level security works
When you view a visualisation in Power BI with row-level security applied, the software starts at the lowest level of granularity, and decides for each row in each table whether it should be included in the visualisation:
If we view our report as the northern manager, we'll only see courses for the shaded rows.
Before setting security, you may first want to make sure that filters applied in one table affect all others. To do this, enable bi-directional filtering. First double-click on the relationship between two tables:
Double-click on the relationship between two tables to edit it (for our example, you'll need to drag the CourseId from one table to the other to create the relationship first).
At the bottom right of the dialog box which appears, you can set cross-filtering to work both ways as follows:
Choose Both to make each table's row filters influence the table to which it's linked.
Creating row filters
You'll find everything to do with row filtering in Power BI Desktop on the Modeling tab of the ribbon:
Select this tab, even if you think it's spelt wrongly!
You can now choose to manage your security roles:
Choose this option to create and edit roles.
For each role that you want to create, click on this button:
Click to create each role.
Type in a name for this role:
We'll start from the bottom of the UK and work our way up ...
For the selected role, you can now assign DAX filters:
Right-click on a table which contains a field you want to filter by, and choose the field.
If you don't know DAX, don't worry too much - it'll be pretty obvious what to do once you've read this blog.
Power BI Desktop will create a template rule for you, which you can change:
|Replace the word Value ...||... with the required venue.|
Testing your new roles
A nice feature is that you can see what data would look like if you adopted any particular role:
Select this tool on the Modeling tab of the ribbon to test your roles.
Choose who you want to be today:
Today, Matthew, I will be Southern manager (unfortunately only people in the UK over a certain age will get this reference to Stars in their Eyes).
You can now see your report through the eyes of one role:
When you've finished, click on the Stop viewing button at the top of the report.
You can use the following symbols to combine conditions:
|Symbol||What it means|
|||||Either of two conditions is true|
|&&||Both of two conditions are true|
For example, the Northern manager role could have filters like this:
The Northern manager can see data for Manchester or Birmingham.
Picking up on the user's identity
You can use two functions to return the user's name:
|Function||What they return|
You're almost certainly better off using the user principal name function. When you publish a report, the UserName() function in Power BI Service switches to returning the user principal name instead.
So now you've created your roles, it's time to publish it and look at what you'll see in Power BI Service.