While working on an Embedded Power BI report, I got a requirement to implement Row Level

Security.  E.g. Region heads can see data of their Region only.

This report is accessible from the .Net MVC application. Locations are assigned to users from the application. This report is hosted on Power BI Server and authenticated by a .Net Application with predefined credentials.

Sample Data:

There are two tables:

1. Finance
2. Users

 

Figure 1:-  finance Table

 

Figure 2:- User

 

Multiple Options to achieve the same with Power BI

We have found out following options to implement the same:

1. RLS (Row Level Security)
2. Through Query String
3. Control report filters that will use in embedded code

There are certain limitations with Option#1 and Option#2 (I will describe them later), so moving on with Option#3 “Control report filters”

 

Implementation with “Control report filters that will use in embedded code”

When you embed a Power BI report, you can apply filters automatically during the loading phase, or you can change filters dynamically after the report is loaded.

For example, you can create your own custom filter pane and automatically apply those filters to reports to show user-specific insights. You can also create a button that allows users to apply filters to the embedded report.

 

Step 1: First Identify the filter based on your requirement

There are five types of filters available

1. Basic - IBasicFilter
2. Advanced - IAdvancedFilter
3. Top N - ITopNFilter
4. Relative date - IRelativeDateFilter
5. Relative time - IRelativeTimeFilter

Based on my requirement I have used a Basic filter and you can use others based on your need. If wants to know more about others click here

Here in this blog I am continuing with the #1 basic filter method

 

Step 2: Determine the table and column that you wants to filter

In my case, I want to filter the table ‘finance’ and my column was ‘country’ because when particular user login the country column must be filtered

For example:- user G1 login the country = ‘Germany’ and this column in table ‘finance’

Table:- ‘finance’   AND Column:- ‘country’

 

Step 3: Put this two things in this code

 

Step 4:  Make Dynamic

You can see into this code that there is ‘values’ where I pass “USA” and “Canada” that is hardcode but we want that dynamic values that have to be changed based on which user is login

That’s why I made variable that contain ‘country’ name which is assigned to particular login user,

For example:- the variable name is ‘Array’ If user G1 login this variable return the value [“Germany”] and set this variable to value

Like:

 

Note: if login person has multiple locations than that variable should return values like

[“Germany”, ”USA”, ”India”]

Note: if login person is manger or CEO they can see all the country data for that

Variable has to return Null instead of blank array [ ]

 

Step 5:  Put This code into embedded code.

Step 5.1: Identify your Method

1. User-owns-data( Embed For your Organization)
2. App-owns-data (Embed for your customer)

Step 5.2: According to your method put this code in the given place For the #1 method Add this code into [EmbedReport.aspx] file

 

Figure 3: User-owns-data

 

For the #2 method Add this code into [EmbedeReport.cshtml] file

Figure 4: App-owns-data

 

Figure 5: Sample

 

And that’s it. Now, try to run the report from the application and it should work as expected. Feel free to reach out to me, if you face any issues.

 

Conclusion

I Hope I’ve Shown You How Easy It Is To Implement Row Level Security, So far we have learned step by step process of it. We started with identifying the filter and then finding out the actual table and column that we want to filter on and generating the code. last but not least put this code into our embedded code.