Using Power BI Object Level Security

By Greg Graham | May 13, 2022

Power BI Object Level Security - Feature

 

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
    = “CAN”
  • For the USA and USAAdmin roles, add this Table filter DAX expression
    = “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.

Related Posts

Recommended Reading:

5.13.22 Power Platform

Using Power BI Object Level Security

  The following article will demonstrate how to use Power BI Object Level Security to disable column data based on […]

Read the Article
5.12.22 Dynamics CRM

How to Use the Stoneridge Support Portal

Stoneridge Software’s support portal is an intuitive and useful function that makes it easy for you to access resources to […]

Read the Article
5.6.22 Dynamics GP

Dynamics GP Transaction Removal: Purchase Orders

  Are you having performance issues with Purchase Orders?  Do you find that there are old Purchase Orders on your […]

Read the Article
5.5.22 Dynamics GP

The Real Story about the Long-Term Future of Dynamics GP Support

I’ve seen a number of people put forward comment that Dynamics GP is going away and you have to get […]

Read the Article

New Features in Dynamics 365 Business Central 2022 Wave 1 Release Part 3 – Financial Enhancements

The Dynamics 365 Businses Central 2022 Wave 1 Release has a lot of new and exciting features to help your […]

Read the Article
4.29.22 Dynamics GP

Dynamics GP Transaction Removals: Bank Reconciliation

  This is part 2 of a 3 part series on Dynamics GP Transaction Removals. These quick tips will hopefully […]

Read the Article
4.28.22 Dynamics GP

Uncommonly Used Features – Integrate Purchasing and Payables to Fixed Assets in Dynamics GP

Being able to integrate Purchasing and Payables to Fixed Assets in Dynamics GP will help you create visibility for your […]

Read the Article

New Features in the Dynamics 365 Business Central 2022 Wave 1 Release Part 2 – Ease-of-Use Features

  There are many new features in the Dynamics 365 Business Central 2022 Wave 1 Release to get excited about! […]

Read the Article
4.22.22 Dynamics GP

Dynamics GP Transaction Removal Series: Receivables

  This is part 1 of a 3 part series on Dynamics GP Transaction Removal. These quick tips will hopefully […]

Read the Article

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