Enabling different roles and giving users access to different levels of data is called row based security.
For example; Managers of different departments should only see data for their own department. Higher level users should be able to see everything
To show how Row level security works, we are going to use the Azure Database AdventureWorksDW
There are two types of Role Based / row level security. Static and Dynamic. The first example will show static row level security.
Row Level Security and Role based security are the same thing.
Power BI Desktop. Get Data
Open Power BI Desktop and go straight into Power BI Desk top. We are going to create the data within Power BI
If you haven’t used this before you may need to log in. Username welcome1 PW trngPeak1
We are going to import two tables, DimSalesTerritory and FactResellerSales
The Report will be very simple as we just want to look at row level security for each Territory
After importing the tables, load the data
In relationships ensure DimSalesTerritory is connected to FactResellerSales by the SalesTerritoryKey
Power BI Desktop. Create Visuals
For this simple report we want the following visualisations:
Stacked Column Chart
Axis: DimSalesTerritory, SalesTerritoryCountry
Value: FactResellerSales, SalesAmount
Field FactResellerSales, SalesAmount
Field: DimSalesTerritory, SalesTerritoryGroup
Field: DimSalesTerritory, SalesTerritoryRegion
Role 1 We have a sales Manager that oversees USA
Role 2 We have a sales Manager that oversees Europe
Role 3 is for Pacific
In Power BI Desktop there is a Security section within the modelling tab
Click on Manage roles and create a new roll
- Create -New Roll. Click … to rename as USA Sales Manager
- Click … Next to DimSalesTerritory > Add Filter and Choose SalesTerritoryCountry and set up for “United States”
- Same again (Create New Role) for the Europe Sales Manager
- Same again (Create New Role) for the Pacific Sales Manager
We now have three roles set up.
Testing roles in the Desktop
To test roles. Go back to Modelling > Security and View as
Stop viewing to go back to viewing all data without Row Level Security
Row Based Security in Service
Now we have three roles, these roles need to be assigned in the service
Publish your reports into Power BI Service. I am publishing to my App workspace Adventureworks Dev
To create an App workspace
Go to Power BI https://powerbi.microsoft.com/en-us/landing/signin/
Go to the workspace and check your report is available
The new Report is called RoleBasedSecurityTestAdventureworks
Next go to Security. Click on … Settings on the data set
Testing roles in the Service
Before assigning roles to a User or Group (As above), you can click on … against each role and Test as role
Once set up, it doesn’t matter how many times you republish a model. It wont cause issues with the security. This kind of security is called Static security because the values have been determined in the DAX expression in the desktop file.
Once you have tested your roles you can add users against each role.The Report is now ready use
This is fine when you are implementing a simple solution above but becomes a pain if you have, for example thousands of roles. employees who want access to their own payroll information.
The next how to guide will go through a basic dynamic security example