Using Power BI Object Level Security

By Greg Graham | May 13, 2022

The following article will demonstrate how to use Power BI Object Level Security to disable column data based on a user’s role. 

Power BI Object Level Security: Test Example

You can get started by inputting the following data in SQL Server…

  • use master
    go
  • create database ObjectLevelSecurityDemo
    go
  • use ObjectLevelSecurityDemo
    go
  • Create table DimCustomer (
    Id int not null primary key
    ,Name nvarchar(20) not null
    ,Country char(3) not null
    ,State nchar(2) not null
    ,Age int not null
    ,GovtId nvarchar(20) not null
    )
  • insert into DimCustomer values (1,'Marky','USA','MA','59','123-45-6789')

    insert into DimCustomer values (2,'Ricky','USA','MA','44','234-56-7890')

    insert into DimCustomer values (3,'Danny','USA','MA','21','345-67-8901')

    insert into DimCustomer values (4,'Terry','USA','MA','16','456-78-9012')

    insert into DimCustomer values (5,'Mikey','USA','MA','32','567-89-0123')

    insert into DimCustomer values (6,'Davey','USA','MA','66','678-90-1234')

    insert into DimCustomer values (7,'Timmy','USA','MA','33','789-01-2345')

    insert into DimCustomer values (8,'Tommy','USA','MA','69','890-12-3456')

    insert into DimCustomer values (9,'Joey','USA','MA','24','901-23-4567')

    insert into DimCustomer values (10,'Robby','USA','MA','88','012-34-5678')

    insert into DimCustomer values (11,'Johnny','USA','IA','38','Unknown')

    insert into DimCustomer values (12,'Brian','USA','IA','83','Unknown')

    insert into DimCustomer values (13,'Meg','CAN','ON','74','123-456-789')

    insert into DimCustomer values (14,'Jo','CAN','ON','54','234-567-890')

    insert into DimCustomer values (15,'Beth','CAN','BC','42','345-678-901')

    insert into DimCustomer values (16,'Amy','CAN','NS','91','456-789-012')
    go

  • Select * from DimCustomer

Then add a trivial Power BI report that looks like this:

Power BI Object Level Security - Power BI Report

Next up, add the following Roles in the Power BI Desktop Editor.

Power BI Object Level Security - Power BI Desktop Editor

  • For the CAN and CANAdmin roles, add this Table filter DAX expression
    [Country] = "CAN"
  • For the USA and USAAdmin roles, add this Table filter DAX expression
    [Country] = "USA"
  • For the Admin roles, you can leave the Table filter DAX expression blank and save.

Enabling Object Level Security is not implemented in the Power BI Desktop editor. In order to do that, you will need to use the Tabular Editor External Tool to enable Object Level Security. You can download that here and add it to the External Tools section of your Power BI Desktop.

Power BI Object Level Security - Tabular Editor

When the Tabular Editor opens, you can complete the security by modifying the CAN and USA roles as follows:

Power BI Object Level Security - Modifying Roles

Then, you can save your changes in the Tabular Editor to test them in the Power BI Desktop.

Power BI Object Level Security - Tabular Editor Test

You will notice the visual with the secured data - GovtID - appears to be broken. You should also note there will be no indication to the report consumer that the GovtId field even exists in the model fields.

Power BI Object Level Security - GovtId Field

If you publish this model to PowerBI.com, you can then apply the report role permissions to the data set.

Power BI Object Level Security - *OLSDemo

Power BI Object Level Security - Row Level Security

Power BI Object Level Security: Additional Notes

Microsoft outlined the following caveat for this demo:

“Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. In the Power BI service, members of a workspace have access to datasets in the workspace. RLS doesn’t restrict this data access. (Contributor Access, for example)

If you publish your Power BI Desktop report to a new workspace experience in the Power BI service, the RLS roles are applied to members who are assigned to the Viewer role in the workspace. Even if Viewers are given Build permissions to the dataset, RLS still applies. For example, if Viewers with Build permissions use Analyze in Excel, their view of the data will be protected by RLS. However, workspace members assigned Admin, Member, or Contributor have edit permission for the dataset and, therefore, RLS doesn’t apply to them. If you want RLS to apply to people in a workspace, you can only assign them the Viewer role. Read more about roles in the new workspaces.”

You can access that full blog post here.

Questions?

Please reach out to us if you have any questions about this or any other Stoneridge software solutions.

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X