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.
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;
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;
And she be part of another role which has access to everything but California with DAX expression below;
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
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.
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.
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.