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