How to set Row Level Security (RLS)

Owned by Ann Crawford (Unlicensed), created
Last updated: Mar 10, 2022
2 min read

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.

You can configure RLS for data models imported into Power BI with Power BI Desktop. You can also configure RLS on datasets that are using DirectQuery, such as SQL Server. For Analysis Services or Azure Analysis Services lives connections, you configure Row-level security in the model, not in Power BI Desktop. The security option will not show up for live connection datasets.

RLS uses the Power BI login user name as the value in dax queries to restrict access. We commonly use netid because it is a unique id.

 

Instructions

  1. Create look up table. One column is the netid and the other is the column that you will be filtering on. This example limits departments that users can see.

    Create a table with dept and net id lookup – for example AcademicRLS

  2. Create Role In Power BI Desktop

    Create a role in power bi desktop > home > manage roles.  In this example the Role is called Deparment based on the table Academic RLS Admin role allows all values so that is usually 1=1 since that will always resolve to true. The Dax formula to extract netid from Power Bi Login for Role in this example is:

    [UCSB NetID] = left(userprincipalname(),find("@",userprincipalname())-1 ) So this will show all rows were the boolean resolved to true for the table AcademicRLS

 

Related articles

https://docs.microsoft.com/en-us/power-bi/enterprise/service-admin-rls

https://idm.dev.my.ucsb.edu/Security/Assignments