Power BI datasets and Object Level Security (OLS)


I once had a customer who had a large collection of data from different subsidiaries. These companies were only allowed to see their own, anonymized data, but the Security Officer of the main company also needed to see the Personal Information (PI) of all employees. 

Eventually we “solved” this by creating two reports, which also means two reports to maintain. The February release of Power BI finally has a solution: Object Level Security (OLS).

In combination with Tabular Editor you can now control access to certain columns or even entire tables for certain roles. In this blog, I’ll show you how!


  I created a simple table with ID, Name, and Phone number:

Information in my table
Information in my table

The next step is to create two roles, I call them consultant and Privacy Officer (PO). A consultant may only see the other names but not the phone number. The PO is allowed to see all the information. In Power BI, go to Modeling -> Manage roles > Create.

Create two roles in Power BI
Create two roles in Power BI

You don’t need to express any filtering at this point, just create the roles. Now, according to the Microsoft blog I should be able to connect with Tabular Editor and apply OLS. To do this, go to External Tools tab and select Tabular Editor. Unfortunately, I can’t apply extra security:

Tabular Editor.
Tabular Editor. 

To solve this, I create a workspace with Premium Per User capacity and then publish my report to that workspace. 

Workspace Settings
Workspace Settings
Publish the report

The next thing I can do is using the Workspace Connection to connect my Tabular Editor with the newly created workspace (you do need XMLA Read/Write for that!). When I am connected with my dataset, I can now use my OLS settings:

Security settings in Tabular Editor
Security settings in Tabular Editor

In the table settings, I can change the permissions per role for complete tables, or only a column. I don’t want consultants to see all the phone numbers, so I change the phone column settings to None for the role Consultant.  

Changing column to none for consultants
Changing column to none for consultants

After you save the changes, we can go back to the Power BI Service and take a look at our roles. You can do this by selecting the dotted menu of the dataset and select Security.

Dataset settings
Dataset settings 

By selecting the menu of a role, you can test the role without actually adding members.

Test as role
Consultant can’t see phone numbers
Consultant can’t see phone numbers

Now as you can see, a table with phone numbers isn’t available for consultants, but it is for the Privacy Officer! This would solve my clients problem by just adding an extra tab in the report. Regular users can then open the tab, but won’t be able to see anything!


I’m still not sure why I can’t change the security settings before publishing it to a workspace. Maybe it isn’t converted to a tabular model before? Feel free to let me know if you got the answer! 

Another thing: what if I want to be able to see my own phone number, but not others? Unfortunately, that’s not possible at this moment. To solve this, the best way is to create another table without the phone number and add row level security (RLS) to the table with phone number. 

You can create a relationship between these tables and create a measure:

_phone =
IF (
ISBLANK (
 VALUES ( ‘Personal Information’[Phone] )
 ),
 “Not allowed”,
 VALUES ( ‘Personal Information’[Phone] )
)

The result is this:

As seen by consultant Erik
As seen by consultant Erik
As seen by the PO
As seen by the PO

Take care.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close