Measure-Level-Security in Microsoft Power BI

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.

Before applying Measure-Level-Security

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.

After applying Measure-Level-Security

Creating the Measures

In our example, we simply use a table that we have created directly in our pbix-file, 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.

pp-measure-level-security-example

.zip

Download ZIP • 238KB

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 username, the email, and the permission column showSales that has a BOOLEAN data type (True or 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 True or 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:

1. Result_ColumnName

2. Search_ColumnName

3. Search_Value1 (2, 3, …)

Compared to 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 True or 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 Permissions[email] (Search_ColumnName).

Since LOOKUPVALUE() returns 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 Other.

Modeling Tab: View as Roles

How would our report look like if we were logged in as Jane Doe?

Measure-Level-Security: Jane is still able to see the sales data

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.

Measure-Level-Security: John is not able to see the sales data

Next Steps

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 john.doe.external@peaksandpies.com. Next, you can use the CONTAINSSTRING function and check for the keyword „external“:

isExternal = CONTAINSSTRING([UPN], "external"