Row Level Security in Power BI

Tags PowerBI DARTS

Row Level Security (RLS) can be used to restrict access to data in a Power BI report.

Instructions 

Row Level Security (RLS) can be used to restrict access to data in a Power BI report based on a user’s Power BI Service login credentials. The RLS has to be set up on each dataset.

Steps

  1. Open your dataset in Power BI Desktop. Select "Manage Roles" under the Modeling tab.
    manage roles
  2.  Create a new role:
    1. Click "Create" and name the role
    2. Choose a field from the appropriate table
    3. Add DAX expression
    4. Create all of the roles you need
    5. Click "Save"
      new role
            
  3. Publish the dataset to Power BI Service.
     
  4. On Power BI Service, go to the dataset > three ellipse> Security. 
    PBI service security
  5. Add member's email address or AD Groups to each role. (All available emails or groups will show up when you start typing. If you don't see the group, please contact DARTS@maine.edu .) Users who are not assigned security roles in Power BI Service will be able to see all the data in the model.row level security on Power BI Service
  6. Test a role
    1. Click the ellipse next to the role
    2. Click "Test as role"

      test as role
       
    3. It will take you to the report page and show the report with the security role.

      view a report
       
    4. Save the security settings    

Additional Resources

Using the DAX function makes it easier to set up the row-level security. Below are the examples.

PATH() Function:

Returns a delimited test string with identifiers for all the parents of the current identifier. This will generate the complete organizational hierarchy based on the staff dimension table.
Org Hierarchy = 
PATH(Org[EMP_ID],Org[Manager])

PATHITEM() Function:

Returns the item from the specific position from a string resulting from using he PATH() function. This can be used to identify a specific level in the hierarchy.
Org Level 1 = 
LOOKUPVALUE('Org'[Emp_UserPrincipalName],
'Org'[EMP_ID],
PATHITEM(Org[Org Hierarchy],1,TEXT)
)

USERPRINCIPALNAME() Function:

Returns the user in the format of their user principal name, i.e. test@maine.edu (email address). To test the role, replace the USERPRINCIPALNAME() function with a hardcoded value.

[UserPrincipalName] = userprincipalname()

External Resource

Environment

  • Power BI