Managing Power BI Row Level Security in a Dynamic Way - Advanced Level

Scenario - I have to allow user only to access a particular region or a particular brand or even a particular section of a workspace with multiple dashboards like sales, supervisor dashboard, agent performance, channel performance, quality, marketing section, etc. In addition to this the access can be revoked as per the mapping :)

You really have to layout a design architecture so that it can be dynamic in future to add a new section. Hence recommended to have a .pbix file to cater to each section. Here I have created around 8 .pbix files which represents various sections as requested by client.

The advantages of having separate .pbix files are many

1. Separate file can be worked upon by several developers so easy to collaborate

2. You can publish all of these under same workspace and have it in single APP

3. If in future you can delete one of the section or rather say unpublish and yeah that's all you have to do if client does not require them in future.

4. You can simply create a new .pbix file and add a section in the add! Simple right?

5. data size - Most important factor is data size would be limited and you have to plug in only those data that is required for that particular section

6. Use dataflow in power BI service to do all transformation and any developer can plug and play with your data

Okay so without deviating further lets understand what are the steps involved in achieving what you are looking for

Step 1: Creating a centralized table to grant and revoke access (my file name is RLS Sample Data)

As shown below in the picture, I have created a dummy excel table and imported into Power BI. You can use any medium like create a jazzy web application or use SharePoint Forms to manage this. but ultimately you would need a data table like below which will be imported in Power BI

1. User_Account_Name: Important to note that this is Email address by which user log in to PowerBI (basically your O365 email ID)

2. Role_Name: Provide as many role as you can. example - I have to restrict user based on region hence region is a role name. Similarly if I have to restrict user based on a brand name, then Brand becomes a role name

3. Role_Code: This is actual data which is there in the transaction table or master table (region, brand are transaction related but what about Section? For this we have a solution and continue reading further

4. Role_Start_Date: Date from when this access will be granted

5. Role_End_Date: Date on which the access will be revoked

6. Validity check - After importing the RLS Access Data, you have to create a custom column which says whether the access is active or expired (Code is given below)

Step 2: Creating a custom column in RLS SampleData

Validity Check = SWITCH(TRUE(),'RLS Sample Data'[Role_End_Date]="Null","Active",VALUE('RLS Sample Data'[Role_End_Date])>=TODAY(),"Active","Expired")

Step 3: Create a dimension table (my file name is dimAffiliatetable)

This is the dimension table that will have country/region information A.K.A master mapping table. This will have 1 to many relationship with transaction table

IMPORTANT -

1. Do not create any sort of relationship between RLS data table with any dimension or transaction table

2. Let there be relationship between dimension table and transaction table as shown below

As shown in below image you can visualize the relationship and design architecture. Dimension table country is related to country of various transaction tables

Step 4: Writing DAX to manage row level security

1. Click on Manage Role on the ribbon section

2. Create a Role as explained

---

Code Below

var vAuthUser=

SELECTCOLUMNS(

Filter('RLS Sample Data','RLS Sample Data'[User_Account_Name]=USERPRINCIPAlNAME() && 'RLS Sample Data'[Role_Code]="Agent Dashboard" && 'RLS Sample Data'[validity check] = "Active"),

"Email",'RLS Sample Data'[User_Account_Name])

var vRegion=

SELECTCOLUMNS(

FILTER(

'RLS Sample Data',

'RLS Sample Data'[User_Account_Name] in vAuthUser),

"Region",'RLS Sample Data'[Role_Code])

return

([Region] IN vRegion)

---

Voila! the job is done. If a user meets the criteria then that region will be accessible to him or her and that too for a particular time period :)