This post is also available in:

Hits: 19730

The use of user roles is an essential element for restricting or preventing access to the data. The following article describes the possibility of displaying measures for specific users only. 

Starting position

Recently, I received an interesting request. The question was about the following task:

1. The management can see everything.
2. The sales manager can see the sales of all employees, but not all measures. In particular, the measures “Product Cost”, “Profit pct” and “Sales Profit” must not be seen.
3. Employees may only see their sales and may not have access to the measures mentioned.
4. The solution should be designed in such a way that it is not possible for the end user to circumvent the restrictions.

The third and fourth part of the question is very easy to implement with user roles (Row Level Security).  Reza has already written some blogs about it. Especially his article “Dynamic Row Level Security with Manager Level Access in Power BI”  forms the basis for my solution.
The pbix file with the solution for today’s post can be downloaded here. Change the file extension from zip to pbix after downloading.

Create the user role

The second part of the request is a bit more demanding, but quite feasible. First, I created a new table called “RLS” in Power Query and assigned different roles to three people. The new table has no relationship to other tables. The remaining data comes from the demo database “AdventureWorks DW 2014”.

Table RLS – Row Level Security per user

The role determines what the individual user is allowed to see. To do this, we need a new user role, which we are now creating. Click on the menu item “Manage Roles” in the tab “Modeling”.

Five steps are necessary in the dialog that has now appeared:

RLS Employee

1. First, click on “Create”.
2. Name the new role according to your taste. You can change the name by clicking on the three dots on the right and renaming the role.
3. Select the table to be filtered.
   Since we want to filter the data depending on the logged in user, this is the table “DimEmployee”.
4. Next, enter the necessary DAX formula. I will explain it below.
5. Click on “Save” to save the new user role.

DAX Formula

In order to achieve dynamic filtering via the user, the DAX formula in the format [User] = Username(). This ensures that each user can only see his data.

In our example, however, we have to check several steps:

1. Who is the currently logged in user?
2. Is the user entered in the “RLS” table? If not, he does not see any data.
3. If he is entered, what role is he assigned? Is he in the role “Sales” and may only see his data, or does he belong to one of the other two roles and may see all data?

First we check who is logged in and whether the user is registered in the table “DimEmployee” and should have access to the data. This is done via [EmailAddress]=USERNAME().

We choose the e-mail address because the user logs into Power BI via his e-mail address. I won’t go into the problems concerning UPN (User Principal Name) here. Reza has also written an excellent article about this.

Next, we have to filter the table “RLS”. For this we use Filter(RLS, RLS[User]=USERNAME()).

Attention: In contrast to the usual DAX notation in the German Power BI versions, the semicolon must be replaced by the comma in the user role definition. I don’t know why, it’s just like that.

After the user has also passed this check, the assignment to the role is still missing. For this we use maxX (Filter(RLS, RLS[User]=USERNAME()) , [Role])

The maxX formula iterates over the “RLS” table and, in conjunction with the previous filter, returns the role of the logged-on user. We check the results for the two options “Board” and “GroupLeader”.

If one of the two conditions apply, the user has access to all data. If not, he can only access his data. If you are not entered in the user table, you cannot access it.

But how can we share all data when we check the user’s username? Quite simply, we need a statement that returns TRUE. In its simplest form this is Formula 1=1, the result of this calculation is always correct.

Compose the DAX Formula

When we put everything together, we get the following formula with the control of the conditions:

The double character || stands for “or”. The DAX equivalent “or” is not allowed in the DAX formula for the user role definition.

Control of the user role

To check whether the DAX formula also works, I activate the role as user “linda3@adventure-works.com”.

Right next to “Manage Roles” you will find “View as Roles”. After activating the dialog opens, in which you have to enter the user and click on role. The result is as follows.

RLS Result Sales
I have created a measure “User” to see if the user has changed. The formula for the measure “User”: User = USERNAME()

Below you can see the table “RLS” with the user name and the role and at the bottom the data of the selected user Linda. For final control we check this with a group leader. This also works and we see from the higher sales that the group leader sees more data.

RLS Groupleader

And again, next to each other to emphasize the difference:

RLS Employee and Groupleader

Display Measures for specific users only

You may have noticed that some measures are empty in the pictures above. This leads us to the last requirement that group leaders and sales should not see every measure, but members of the management should.

I also achieved this via the user control, in two steps. First, I added a third column to the “RLS”. This defines whether a user may see all measures (1) or not (0).

Table RLS Show Measure

Secondly, I created a new DAX formula under “Manage Roles” for the table “RLS”:

The only difference from the previous formula is the reference to the RLS[User] column.
But that alone is not enough. We still have to define the five measures and an auxiliary measure.

Definition of the measures

First, the simple measures:

Sales Amount = SUM(FactResellerSales[ExtendedAmount])

Sales Units = SUM(FactResellerSales[OrderQuantity])

The “helping” measure controls the display of the remaining three measures:

Lookupvalue returns the value from the “ShowMeasures” column of the “RLS” table if the user (RLS[User]) corresponds to the logged in user[Username]. The return value is 1 or 0 according to the table definition.

And with this value we can calculate the remaining measures by multiplying the result of the RLS_Measure by the result of the other measure and, if necessary, returning a zero blank.

Final result

And the result of all the previous steps is as follows for John Doe, who is allowed to see everything:

RLS Board member

The result also works with visualizations, here using a bar chart as an example. I put the results for all three roles next to each other. Only John Doe sees all measures and Linda sees her data only.

RLS Diagramms

Of course, the users still must be assigned to the role created in the Power BI Service. In the articles mentioned at the beginning, you will find instructions.

Conclusion

With the application of user roles, solid DAX knowledge and some imagination, even more complex scenarios can be solved. I hope you can take something out of the post.

If you have another approach, please leave a comment or write me. Write also, if you have questions or comments.

You can download the pbix file with the solution here. Changes the file extension from zip to pbix after downloading.

Happy Querying!