Adding row-level security to Power BI reports
Part two of a four-part series of blogs

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!

  1. Securing Power BI Reports and Dashboards
  2. Row-level security in Power BI Desktop (this blog)
  3. Row-level security in Power BI Service
  4. Who can see what in your reports

This blog is part of a much longer series, which together comprise a full online training course in Power BI Service.  You can see get details of our classsrom Power BI training courses here.

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:

Rows for northern example

If we view our report as the northern manager, we'll only see courses for the shaded rows.

Setting cross-filtering

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:

Editing relationship

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 for cross-filtering

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:

The Modeling tab

Select this tab, even if you think it's spelt wrongly!

You can now choose to manage your security roles:

Managing security roles

Choose this option to create and edit roles.

 

For each role that you want to create, click on this button:

Creating a role

Click to create each role.

 

Type in a name for this role:

Southern manager 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:

Assigning DAX filter

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:

View as role

Select this tool on the Modeling tab of the ribbon to test your roles.

 

Choose who you want to be today:

Choosing a role

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:

Viewing as a role

When you've finished, click on the Stop viewing button at the top of the report.

Combining conditions

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:

Northern manager role

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
UserName() DOMAIN\FirstName.LastName
UserPrincipalName() FirstName.LastName@DOMAIN.LOCAL

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. 

This blog has 0 threads Add post