EN – Dynamic Role Level Security in Power BI

Introduction:

Row level security is another aspect of sharing is the security on the data set. Enabling different roles and giving users access to different levels of data is called Row Level Security. But every time it is not possible to assign the role to newly added individuals so this post explains the details of Dynamic Role Level Security in Power BI and how to configure it in Power BI Desktop and Power BI Service. Role Level Security enables you to apply security to roles and adds users to each role dynamically.

Case Study:

Consider we have three level of employees in the Organization like Director, Manager and Associate. There is an annual survey of various Countries on GDP, Population and Literacy Rate. The survey report is showing the data from 2015 to 2017 for India, USA, China & Japan.

Now we need to build the report in such a way that the Director can access all Countries info and the Manager can access the info of those Countries where his/her Associates working and an Associate can only view his/her working Country info.

Solution:

Here we can apply Row Level Security but in a Dynamic way.

I have two tables:

Country_Details ( UserID refers to the Associate ID)

User_Details ( UserID refers to the Associate ID)

This table has a field which is “IsDirector”, values are zero or one. If the value is one, then the user is a Director and can see everything if the value is zero, then user should be able to see his/her only data rows (if the user is an Associate). To get the Manager level we need to define the Organizational Hierarchy using DAX as follow:

Now your Organizational Hierarchy column contains data like

40|31|30 (Here 40 is the ID of Director, 31 is the ID of Manager and 30 is the ID of Associate)

If the output of Organizational Hierarchy is like 40|31 then here 31 is the Associate’s ID (who is the Manager for ID’s 30 & 32) and 40 is the ID of his Manager who is also the Director.

Now the next step is to find out the Email ID for various Organizational level employees. For this we need to apply DAX as follows:

As we know there are three levels of employees so we need to create three columns like Org Level 3, Org Level 2 and Org Level 1. Here is the DAX for reference:

Org Level 3 = LOOKUPVALUE ( User_Details[UserEmail], User_Details[UserID], PATHITEM ( User_Details[Organisational Hierarchy], 3, 1 ) )

Now the updated User_Details table is:

Here we need to add one more calculated column to show the last log on user. For that we can use the USERNAME()

Last Log On User = USERNAME()

Then after doing all these preprocessing things we then need to assign the Roles as per the steps described below:

Getting Started:

Step 1:

Click on Modeling Tab of Power BI desktop and select Manage Roles.

Step 2:

Click on Create Role and Name the Role:

Step 3:

Now select the Table on which you want to apply the Role Level Functionality. (Here I selected User_Details to apply the functionality).

Step 4:

This is the crucial step as here we need to assign the roles which are based on the query like below:

Explanation:

The first step is to identify who is the person logged into the report in Power BI Service. This can be achieved with USERNAME() or USERPRINCIPALNAME() functions in DAX.

‘User_Details'[UserEmail]=Username()

Then we need to find out whether the logged in user is Director or not. For that the following query can help:

IF( MaxX( Filter( ‘User_Details’, ‘User_Details'[UserEmail]=Username()) ,’User_Details'[IsDirector])=0)

In the expression above, we are using FILTER () to identify all rows from the User_Details table, where the email address matches the logged in user. Then we get the maximum [IsDirector] value from that using MAXX () function. If the result of the expression above is 1, then the person is a Director, otherwise not.

Next step to determine what to show if the logged in user is not Director. For that we can use the Org Level columns that we created earlier as follows:

‘User_Details'[UserEmail]=Username() || ‘User_Details'[Org Level 3]=Username()|| ‘User_Details'[Org Level 2]=Username()|| ‘User_Details'[Org Level 1]=Username()

This means it will search for different Organizational level users and if the logged in user ID matches with the searched ID then it will display the respective Country info.

Now the condition arises where the logged in user is the Director. For this we need to show all Countries info. Here we need to add a very simple DAX which is 1=1 that always returns true as a result.

So the Complete query is as below:

IF( MaxX( Filter( ‘User_Details’, ‘User_Details'[UserEmail]=Username()) ,’User_Details'[IsDirector])=0, (‘User_Details'[UserEmail]=Username() || ‘User_Details'[Org Level 3]=Username() || ‘User_Details'[Org Level 2]=Username() || ‘User_Details'[Org Level 1]=Username()), 1=1 )

Step 5:

Save the query. And save & publish the report to Power BI Service

Step 6:

Right click on the (…) after your Dataset. And then click on the Security Tab.

After clicking on the Security tab, a pop up will come where you need to include the Employee ID.

Add the User and click on Save. And it’s done.

Test The Role in Power BI Service:

Now you can check the Report with the Roles by clicking the (…) and Test as Role.

You can add the ID of the Director to check the report whether it’s showing the complete info or not.

Here Liju is the Director so he can view the complete info. Click on Apply.

Now test with Manager’s ID.

Director’s Report View

Manager’s Report View

The above Manager can view 3 Countries data as there are 3 Associates working under him in 3 different Countries like China, India and USA.

Now we can choose one of the Associate to analyze his view of report.

Click on Apply.

Then we need to share the report with these users so that when they log in to the service they can access their respective report view.