Almost couple of weeks ago Power BI update added the new functionality in security. This functionality enables you to apply row level security to roles and add users under each role. This is really helpful when you want people from one branch, city, department, or store to be able to only see their part of the data and not the whole data set. Power BI applies that through a row level security configuration on the Power BI model itself. So regardless of what source you are importing your data from, you can apply row level security on it. In this post I’ll explore how to use Row-Level Security in Power BI.

Important Note

The method mentioned in this post is now changed due to the update of Power BI Desktop, read the current method in my other blog post here.

Prerequisite

For the example of this post you need the report and dashboard built on Pubs SQL Server database which explained earlier in this post of Power BI online book.

Introduction to Row Level Security in Tabular Model

Row Level Security isn’t new for tabular models. SSAS Tabular had this feature from SQL Server 2012. In SSAS Tabular you could apply security based on DAX expressions on each table or field. You can even apply dynamic security based on upcoming user with USERNAME function of DAX. Power BI is using same model for security. You can write DAX expressions to filter specific part of the data for a role. Each role can have one or more members. Members should be from your organizations to be part of the role for this type of security. Let’s see how easy is to apply this security configuration.

Security on Data Set Level

Security only can be applied from Power BI website at this stage. For applying row level security in Power BI you have to do it on data set level.

Expand Datasets area in Power BI website, click on ellipsis button on pubs data set, and then choose Security. And note that this option works for both Live connections and Imported data.

In Row-Level Security section add a new Role. You’ll see an information bar mentioning that any changes you make in this section will be OVER WRITTEN when you re-publish Power BI report. (this is one of the current limitation of RLS  – Row Level Security – which I’ll discuss later on in this post);

In Role section just create a role with a name, and add members (from your organization);

Now go to the Rules section. You will see list of all tables in your data set. Click on any of them (Store for this example), and write the DAX expression in the text box provided. DAX expression can simply work like a filter. For example if I write expression below;

[state]="CA"

This means that members of this role will only be able to see data related to California state, and all related data to it from other tables. Let’s see how it works on members of this role. But before doing that I have to Share the dashboard containing report elements from this data set to members of this role. So I’ll Share the dashboard with click on ellipsis button on the dashboard and then Share option.

After adding the user to share tab. I’ll be able to see it in Access tab;

Now when user log in they will see the dashboard and report with all elements but in a restricted data set view; they only see California related information;

Users with Multiple Roles

As you might already think of, each user can have multiple roles. This is totally true. you can have multiple roles. each role can have multiple users. and it is possible that a user be in more than one role. I’ve created another role for Washington Sales Manager, and authorized that to WA as below

And then what I see for this user (which now is part of both roles for California and Washington) is a UNION view of both roles. the sales amount below is total of California sales plus Washington, as well as all other data set.

Well, this is a good feature, but sometimes cause ambiguity. For example If Leila as the user has a role to see all data for California state as below;

[state]="CA"

And she be part of another role which has access to everything but California with DAX expression below;

[state]<>"CA"

Then what she will see is EVERYTHING! which is not probably right. I believe this type of issues will be fixed in next updates of row-level security. In the meantime be careful that your roles doesn’t contradict each other in a way illustrated here.

Row Level Security with Live Connection

Despite the fact that Power BI blog post mentioned this feature actually WORKS with DirectQuery or Live Connections. Here is the result when I apply that on my Live On-Premises demo;

This is the RLS applied version of the total below:

For this model I created a role to access only 2008 Calendar Year data as below;

As you can see the feature works with Live connection which is one of the great aspects of this feature I believe. Because I don’t need to apply security on another place.

Limitations

The big limitation for now is that when you change your Power BI model, or report and re-publish it, you will loose all row-level security applied on it on the website. And you have to create it again. This feature at this stage is in preview mode, my strong believe is that there will be an update for this limitation. The least thing Power BI team can do is ability to back up a role and restore it again.

Power Q&A isn’t available for models with RLS enabled.

Some other limitations that listed here, Which I believe most of them will be fixed when the final release of this feature be available.

Summary

Adding row level security is a great step forward in Power BI security configuration. This feature is still in preview mode so expect to see some bugs and issues, however they will be fixed soon in the final release I believe. Security area is still wide for Power BI and lots of stuff can be done in this area, but I see this feature as a very good step towards future security configuration.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.