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. 


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.


There are 2 main types of RLS: 

  • Dynamic RLS
    • Using the variable "userprincipalname()" and a mapping of which for example dimensions a user has access to in a separate table. 
    • We strongly prefer Dynamic RLS
    • Machine generated alternative text:
x 
Manage roles 
Roles 
Data Deparment 
Tables 
CUSTOMER TYPES 
CUSTOMERS 
CUSTOMERS GS ACCOUNTANT I 
CUSTOMERS GS CUSTOMER RES 
CUSTOMERS GS PAYROLL ACCO 
DEPARTMENTS 
EMPLCYEE TIME 
MPLOYEES 
Table filter DAX expression 
ERAIL] =
  • Static RLS
    • Hard coding RLS to "Roles" and then in Power BI Service assigning users to roles.
    • Machine generated alternative text:
x 
Manage roles 
Roles 
Tables 
Deparment 
CONTACTS 
userprincipalname 
CUSTOMER TYPES 
CUSTOMERS 
CUSTOMERS GS ACCOUNTANT I 
CUSTOMERS GS CUSTOMER RES 
CUSTOMERS GS 
pAYROLL ACCO 
ID 
EPARTMENTS 
Table filter DAX expression 
NAME)


Before you set up RLS: 

  1. Make sure you have a BI Book Premium Analytics Licence (Grants access to Power BI Service)
  2. Open your Power BI model in Power BI Desktop and import a table containing the users and the roles you want the users to have. The table can come from BI Book Dataroom, MS Teams or any other source. 
    1. Role is not necessary if user rights are contained in the underlying data (for example time entries) 


Setting up RLS: 

  1. Open your Power BI model in Power BI Desktop and add the role to the user table you want. 
    1. Manage Roles
    2. Create a new role
      1. Select the table you want to apply the filtering on
      2. Write the dax expression like this: [YOUR_FILTER_COLUMN_NAME]=userprincipalname()
      3. Machine generated alternative text:
x 
Manage roles 
Roles 
Data Deparment 
Tables 
CUSTOMER TYPES 
CUSTOMERS 
CUSTOMERS GS ACCOUNTANT I 
CUSTOMERS GS CUSTOMER RES 
CUSTOMERS GS PAYROLL ACCO 
DEPARTMENTS 
EMPLCYEE TIME 
MPLOYEES 
Table filter DAX expression 
ERAIL] =
  2. Validate the Relationships that you applied the security filters on.  We do not assume responsibility for wrongly configured RLS.  
  3. Validate desired result using a username: 
    1. Machine generated alternative text:
x 
View as roles 
O None 
Other user 
O Data Deparment 
userprincipalname 
Cancel


Example models: 

  1. Example model: 
    1. Machine generated alternative text:
Dimensionsltems 1 
Dimensionltem 
Expand v 
General Ledger 
AccountDimensionKey 
AccountDimensionName 
AccountNumber 
Company * Account Dimen... 
Company * Currency 
Company 
CompanyName 
De script ion 
Dimension I 
Collapse A 
a Oikeudet 
Dimension Item 
Email 
Collapse A 
Cardinality 
Many to one (f: I) 
Make this relationship active 
Assume referential integrity 
Käyttäjät 
Email 
Collapse A 
Cross filter direction 
Both 
ply security filter in both directions
  2. One way relation + example function: 
    1. Machine generated alternative text:
Manage roles 
Roles 
erPrincipaIName 
Tables 
DimensionFiIterList 
Dimensions For Analysis 
-mensionsltems I 
Dimensionsltems 2 
x 
Table filter DAX expression 
[Dimensionltem] IN SELECTCOLUMNS ( 
FILTER ( 'Oikeudet', RELATED ( 'Käyttäjät' [Email] ) 
USERPRINCIPALNAME () 
"Dimensionltem" , [Dimensionltem)
  3. RLS without a relationship in relationship view: 
    1. Machine generated alternative text:
Manage roles 
Roles 
luserpnpa 
Tables 
Dimensions For Analysis 
ID, 
-mensionsltems I 
Dimensionsltems 2 
x 
Table filter DAX expression 
[Dimensionltem] IN CALCULATETA8LE ( VALUES 
( 'Oikeudet [Dimensionltem] ) 
' Käyttäjät ' [Email] 
USERPRINCIPALNANE () )



More instructions can be found on Microsoft's page here: : https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls