Power bi row level security tutorial

In this power bi tutorial, let us see how to implement Power bi row level security and column level security in Power BI desktop. Also, we check the below points:

Power bi row level security
How to set row level security in Power BI
power bi row level security based on username
power bi row level security based on email
Power BI column level security
How to set column-level security in power bi desktop
What is table level security in power bi?
Set table-level security in power bi desktop
What is object level security in Power BI
How object-level security in power bi improves the model security
How to enable object-level security in power bi
How to set object-level security in power bi desktop

Table of Contents

Power bi row level security

The power bi row level security is used to restrict the data access for the given user.
In power bi filters restrict data access at the row level, and also you can filter within roles.
But in the Power bi service, users of a workspace have access to a dataset in the workspace. Row-level security does not restrict this data access.
We can configure the Row-level security for the data model imported into Power Bi with Power Bi desktop.
We can configure the row level security on datasets that are using the direct query, such as SQL Server.
For example, there is a sales table, which contains sales data for your company. And the sales information is about products that are sold all over the country. The Sales representatives are assigned to a different region of the country, whether that can be Southeast or Central US etc. So the sales representative in each region should only be able to see sales information that pertains to them and their region.
If the sales director or manager wants to see the sales information across all the regions, so this can be set up with row-level security. And also this is done by filtering down the table that pertains to each row that belongs to each region.

Read: Power bi calculated column vs measure

How to set Power bi row level security

Here we will see how to set row level security in Power bi.

Scenario

I have already created a sales report on power bi desktop by using the sales data.
Then I will create a role called sales reps. Who can only see the data of their region, for that I will use the Table filter DAX expression?
Then we will see how to add members to that role, for that I will publish the report to power bi service and then we will add members who having sales reps role.

power bi row level security — how to set row-level security in power bi

Step 1: Create a role and provide row level security in Power bi desktop

In the Power bi desktop, click on Modeling present in the ribbon. Then click on Manage roles.
Click on Create, under roles. Add a name as sales reps.
Then under tables, next to the table name, click on the More icon -> Add filter -> then select a column i.e. customer location

Then under Table filter DAX expression, you add the row in the DAX expression.
While in Table filters DAX expression, filter the data that this role (Sales reps) can see by entering a DAX filter expression, that returns a true or false value.
Now in the DAX filter expression, replace the value with row data present in the Customer Location column i.e. [Customer Location] = “Florida”.
After adding the DAX expression, select the checkmark icon. Then click on Save.

how to set row-level security in power bi desktop

In modeling tab, click on View as and select sales reps. Then click on OK.

row level security in microsoft power bi

Now in the below screen-shot, as a sales rep, you can only see the customer location which is Florida.
And all other rows present in the Customer location column are restricted by the row-level security in power bi. Then click on Stop viewing to stop viewing as Sales reps.

How to set row-level security in power bi

Step 2: Add a member to the role in power bi service.

To add members to the role, first, we have to publish the report.
To publish the report, Select the Home -> Click on Publish icon.
Once you publish the report to power bi service, open the Power bi service by clicking here.
Then click on My workspace present in the navigation bar.
Navigate to the published report in my workspace, and click on the More icon->Security.

Now in the Row level security page, under Members add people or group. Then click on Add button.
Then click on Save.

In this we can set the row level security in power bi desktop.

Read: How to Embed Power BI Report in SharePoint Online

Power bi row level security based on username

Here we will see power bi row level security based on username.

Scenario

I have used the two sample data tables: sales details and sales reps details.
I have already created the report on the power bi desktop using the sample data.
In the model, I will create the relationship between two tables.
Then I will create a DAX function to return the user’s User principal name.
Then we will use the username() in row-level security in power bi service.

In the two tables, I have created, there is one similar column i.e. customer location. In the below screenshot, you can see the two tables.
Sales list table

Sales reps tables

Microsoft power bi row level security based on the username

Lets see how to create relationship between two tables.

In the power bi desktop, Click on the Model
Then click Manage relationship from the ribbon.

Power bi row level security based on username

Click on Edit. Under Select table and Columns that are related, select the table. It will automatically take the another table.

Microsoft power bi row level security based on user name

Under the Cardinality select many to many. Then under cross filter direction, select Single.
Check the box next to the Make the relationship active. Click on Ok.

Now go to modeling present in the ribbon in power bi desktop.
Click on Manage Roles.
Click on Create to create a role called Sales reps.
Under tables Add filter on the Sales Reps table and select column Sales reps.
Under the Table filter and DAX function, add DAX function i.e [Sales reps] = username()

Go to Home page in power bi desktop. Click on the new measure to see the return value of username().

Power Bi row level security in power bi desktop

Give the measure name as user, then write the DAX function user= username(). Click on Tick to add the measure

power bi row level security based on username

The DAX function i.e. username() in power bi desktop, return windows account.
Create a table visualization and add the user measure to see the windows account.

Now go to the Home page, then click on Publish, to Publish the report to power bi service.
After publishing the report, go to the Power bi service.
Then click on the workspace in power bi service. On the Workspace page, click on the More icon present next to the published dataset.
Select the security

Under add members, add the members. Click on Add.

Then next to the role, click on the Test as a role.

Power bi row level security in power bi service

Now you can see the report as a login username in power bi and I have login as my tenant username. I can only access the report of two locations i.e Alaska and Chicago

This is how to implement Power bi row level security based on username.

Power bi row level security based on email

Here we will see the power bi row level security based on email in power bi desktop.

Scenario

I have already created the report on the power bi desktop. Then I will add create the role in power bi Desktop.
I will add the email address based on the current users.

Power bi row level security based on email — power bi report

In power bi desktop, Click on Modelling.
Then click on Manage roles to create a new role.
Click on Create. Then create a role known as Sales reps.
Under the Tables section, next to the Sales reps click on the more icon.
Select Add filter and select a column in which column you want to filter it.
Here I have taken the customer’s location column.
Then I will add DAX to apply row-level security i.e. [Customer Location] = “Arizona” || [Customer Location] = “Chicago”. Click on the check icon. After that click on Save.

Then modelling tab in power bi desktop. Click on the View as.
Select Sales reps. Then select the other user and add email. Then click on OK.

Now you can see the report based on the role and email. Then click on Stop viewing.

This is how to implement Power bi row level security based on email.

Column level security in Power bi desktop

Column level security (CLS) in Power Bi provides, which column users have access to.
In Power Bi, the column level security is not there. To provide column-level security we use the Tabular model.
For example, if you think about the HR person that should be able to see all the sales data in all regions, and he can able to see the salary level information of the employee. But the sales representative should not have access to the salary level of information. In power bi, this must be a separate report because of row-level security(RLS), there is no way to filter out a column like a salary level, for instance.

Read: 5 Simple and Powerful Power BI dashboard examples

Set column level security in Power bi desktop (tabular model)

Here we will see how to set up column level security in power bi desktop by using the tabular model.

Scenario

I have created an HR report in power bi desktop by using the sample data.
In this sample data, there is a column called average training score, which cannot access by the Viewers Role, to set this restriction we have to set column-level security for that column in power bi desktop.
Here we have to create the two roles, i.e
- HR roles
- Viewers roles

Follow the below steps to set the column-level security in power bi desktop:

Step 1: Create the Roles

Click on the Modelling in the ribbon of power bi desktop.
Then click on the Manage roles, to create the roles in the Power Bi desktop.

column-level security in Power bi — Manage roles in power bi desktop

Then click on create, add a name to the new role i.e. HR.
In the same way, add the viewer role. Then click on Save.

set column level security in the microsoft power bi — Manage roles in Power bi

Step 2: Add column level security according to roles

Click on External tools present in the ribbon of Power Bi desktop.
Then click on the Tabular Editor.

In tabular editor, expand the Roles section, to see the different roles.

How to set column-level security in power bi desktop — Roles in the tabular model in Power bi

Then expand the Table section and expand the table in which you want to add the column level security.
Then select the column i.e. average training course.
On the right side go down, under translation, perspectives, security expand Object Level Security and expand the Viewers Role and select none.
Go to File ->Save.

How to set column-level security in power bi desktop by step

Now go to Modelling, select View as -> Viewers. Click on OK.

column-level security in power bi desktop by step — view as roles in power bi

Now as a viewer role, we cannot see the report where the average training score column is used, and also it is restricted from the field section.
To stop viewing as a viewer, click on Stop Viewing.

Now I have selected view as HR, I can see the report which are restricted for viewers.

In this way we can set column level security in power bi desktop.

Read: How to Create a Dashboard in Power Bi?

Power bi table level security

By using power bi table level security, we can eliminate the entire tables from a power bi report. And also you can do this in any model, not just tabular.
To get this, we put table name equals false, and that would filter out the table from the entire power bi report depending on certain roles that we have set up.
Another key thing to be noted is that table and column level security can break certain visuals in power bi.
If we put the certain column into a power bi visual, and that visual no longer contains that column, it will break that visual, and no one will be able to see it if that column or table is missing.

How to set table level security in power bi desktop

Here we will see how to set table level security in power bi desktop by using tabular model.

Scenario:

I have created a report using a Sample dataset of and then I will create a role called viewers.
By using a tabular model, I will add table-level security for viewers.

table level security in microsoft power bi

Step 1: Create role

Click on the Modelling in the ribbon of power bi desktop.
Then click on the Manage roles, to create the roles in the Power Bi desktop.
Then click on create, add a name to the new role i.e. viewer.

Step 2: Table level security according to roles

Click on External tools present in the ribbon of Power Bi desktop.
Then click on the Tabular Editor.

table level security in microsoft power bi desktop

In the tabular model, under roles, you can see the roles we have created.
Under the tables section, select the table, in which table you want to add the security level.
On the right side, under translation, perspective, security, expand the object level security.
Select the role i.e. Viewer and edit the rule default to none. Go to File -> Save.

Table level security in power bi desktop

Now go to Modelling, select View as -> Viewers. Click on OK.

How to set table-level security in power bi desktop

Now as a viewer you cannot see any of the visuals and also in the Field pane, you cannot access the table.

In this way we can set the table level security in power bi desktop by using the Table editor.

Read: How to create Power BI report from SharePoint list + Excel

Power bi object level security

The object-level security in power bi, enables model owners to secure specific tables or columns from report viewers.
As a viewer standpoint, the tables or column simply does not exist.
With the object level security in power bi, we cannot only restrict the access of data but also can restrict the sensitive object names.
This prevents users from accessing sensitive data such as employee, financial records.
All metadata in power bi reports that are opened in power bi service that includes the Object-level security in caching layer.

How object-level security improves model security

Here we discuss how object-level security improves the model security in Power bi, and let’s compare it with row-level security or perspectives

The Row-level security in power bi is a feature that enables, you to protect data by assigning views to roles. By defining the DAX filter restrict row data access for roles that do not have require permission.
The user can view the specific metadata in the power bi model.
With RLS you can also hide the table and column from users, but it is not a security feature. And also RLS does not prevent a user from accessing them via DAX.
Whereas object-level security in power bi, not only hides tables and columns but also secure them.
A user without permission cannot access the secured power bi metadata objects via DAX or any other method.
If the viewers don’t have to require permission, then the table or column simply does not exist.

How to enable object level security in power bi

Here we will see how to enable object level security in power bi.

Like row-level security in power bi, we can define the object-level security within the power bi models.
Currently, Object-level security definition is not created in power bi desktop, therefore we will use the external tools i.e. Tabular Editor.

Set object level security in Power bi desktop using Table Editor

Here we will see how to set object level security in Power bi desktop using table editor.

Scenario

I have created a report using the sample data. Then we will create roles i.e. viewer and HR.
Then we will set the Object-level security definition for the roles.
Then we will publish the report to the power bi service, and navigate to the security and assign the member to the roles.

Click on the Modelling in the ribbon of power bi desktop.
Then click on the Manage roles, to create the roles in the Power Bi desktop.
Then click on create, add a name to the new role i.e. viewer.
In the same way, add the HR role. Then click on Save.

Object level security in microsoft power bi

Step 2: Set the Object level security definition for the roles

Click on External tools present in the ribbon of Power Bi desktop.
Then click on the Tabular Editor.

Object level security in power bi desktop

In tabular models expand the roles, you can see the roles you have created.
Select a role, in which you will assign object-level security definition.
On the right side under security, expand the Table permission, edit the rule none or read. Click on Save.

object level security in microsoft power bi desktop

Step 3: Add a member to the role in power bi service.

Publish the report in the power bi service, by clicking the publish icon present in the home tab on the power bi desktop.
Open the power bi service by using app.powerbi.com. Click on the My workspace from the navigation.
Then click on the More icon present next to the published report. Select the security.

object level security in microsoft power bi service

Now in the Row-level security page, under Members add people or group. Then click on Add button.

Object level security in power bi service

Now at this point, object-level security is defined. The user who doesn’t have the required permission cannot access the field.

In this way you can define the object level security for roles in power bi desktop.

You may like the following Power BI tutorials:

In this Power bi tutorial we learned about power bi row level security and the below things:

Power bi row level security
How to set row level security in Power BI
power bi row level security based on username
power bi row level security based on email
power bi row level security for external users
power bi row level security multiple roles
Power BI column level security
How to set column-level security in power bi desktop
What is table level security in power bi?
Set table-level security in power bi desktop
What is object level security in Power BI
How object-level security in power bi improves the model security
How to enable object-level security in power bi
How to set object-level security in power bi desktop

Bijay Kumar

After working for more than 15 years in Microsoft technologies like SharePoint, Office 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (9 times). I have also worked in companies like HP, TCS, KPIT, etc.