Sometimes it is necessary to restrict the data access for several users. In this case Power BI provides something called „Row-Level-Security“, or RLS for short.
Though RLS might work just fine most of the time, we would advise against it if you need a more granular level of control. In this blog post we would like to introduce what we call „Measure-Level-Security“, or MLS. The essence of MLS is the same as RLS, though its main advantage is integrating the permissions right into your measures. All you need is a table or an external file with your users and permissions set up.
Before we get into the details, let’s have a look at what we want to achieve. In the picture below we can see that the user John Doe has permission to see the sales data.
But maybe we want to restrict his access to this information, so that John can’t see these data points anymore. As you can see in the next picture, he is not able to see the sales data after we apply Measure-Level-Security.
Creating the Measures
In our example, we simply use a table that we have created directly in our pbix-file (click here to download the *.pbix), but you could also use an Excel-file located in your SharePoint-folder, which can be access restricted. We strongly recommend the latter approach for your production environment, as only people who can access and edit the file can change the permissions this way.
In our demo case we have sales data and only two users, Jane and John; of which only Jane should be allowed to see the sales figures. For the sales amount you would normally create a simple measure:
Sales Amount = SUM( Sales[Amount] )
In order to set the permissions, we need something to identify the specific user. Fortunately, Power BI provides us with a function that returns the „User Principal Name,“ which is the email address of a user that is logged into Power BI. We will call this measures „UPN“ and create it with this line:
UPN = LOWER( USERPRINCIPALNAME() )
As mentioned, we created a
Permissions table, which includes the
showSales that has a
BOOLEAN data type (
False). The advantage using a boolean value in our
showSales column is, since we will check for permissions with the
IF()-function, which takes a
LogicalTest as its first argument and returns
False, we can simply reference our measure and keep our code shorter.
Using LOOKUPVALUE() to Retrieve User Permissions
Next, we need to create another measure, which we will call
Show Sales. This is where we will implement our permissions logic based on our
[UPN] measure. We will use it to perform a
lookup in the
Permissions table with the
LOOKUPVALUE() function, which is analogous to Excel’s
VLOOKUP(), and returns
Blank if no value was found. The function takes at least three arguments:
- Search_Value1 (2, 3, …)
VLOOKUP() in Excel, the order in the
LOOKUPVALUE() function is the other way around. First, we need to tell the function which column we want to retrieve the result from. Second, we need to specify the column where the function should search. Finally, we need to provide the function with a value that should be used for the lookup. In our case the result, or the permission, is filed in the column
Permissions[showSales] (Result_ColumnName), and could be either
False. For the look up we use the email address of the logged in user, which is connected to our
[UPN] measure (Search_Value1). We can now match this email address with the one in the column
Blank if nothing is matched, we will wrap it into another function to return
False instead, and use
False as the default value. At the end, the code for the
Show Sales measure looks like this:
Show Sales = VAR permission = LOOKUPVALUE ( Permissions[showSales] , Permissions[email] , [UPN] ) RETURN IF ( ISBLANK(permission) , FALSE() , permission )
Extend the Measures with Measure-Level-Security
Now we have everything in place to extend our
[Sales Amount] measure with Measure-Level-Security. We will use the
IF() function again, but return an empty string value
"" as the
ResultIfFalse parameter value. With this little addition, we assure that all other calculations will work and, for instance, that the total values in our tables, or in our case the fruit names, will be displayed. The final expression looks like the following:
Sales Amount = IF ( [Show Sales] , SUM(Sales[Amount]) , "" )
Check User Permissions With the „View as Roles“ Feature
Let’s now check if our permission filter works. We will use the
View as Roles feature, which you can find in the
Modeling Tab. We click the
View as Roles button and type the email address we want to check next into the field
How would our report look like if we were logged in as Jane Doe?
And what about John Doe? Is he still able to see the sales data? As we know from the beginning of the article, he isn’t.
You can also consider broader permissions based on the email address itself. Hence, you do not need a table or external file. Maybe your external business partners have an organisational email that includes „external“ in the name, like
email@example.com. Next, you can use the
CONTAINSSTRING function and check for the keyword „external“:
isExternal = CONTAINSSTRING([UPN], "external")
Of course, it ultimately depends on your specific use case, but hopefully we were able to show you a helpful approach. Feel free to contact us and let us know if we can help you improve your business even more.