I have written while ago, about how to implement a dynamic row level security in Power BI. This post is an addition to that post. I’ve had a lot of inquiries that; “What If I want users to see their own data, and the Manager to see everything?”, or “How to add Manager or Director Level access to the dynamic row level security?” This post will answer this question. In this post, you will learn a scenario that you can implement a dynamic row level security. In this scenario, everyone will see their own data, but the manager will see everything. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.
Video
Row Level Security Intro Guide
In Power BI, you can implement a row level security in a way that user has restricted access to the content in the report. This is called Row Level Security. There are different ways of implementing row level security in Power BI. Let’s see different types of it;
Static Row Level Security
The logic of security is static in the role definition, we call it Static Row Level Security. to learn more about it, read this blog post.
Row Level Security in SSAS Live Connection
In this case, the Power BI Report connected live to an SSAS model. the username will pass through effective username connection information, to learn more about it, read this post.
Dynamic Row Level Security
When you have too many roles, then implementing static roles is not an option. You need to create one role and maintain the logic of security within the data model. This is called Dynamic row level security. To learn more about this, read this blog post.
Addition to Dynamic RLS
The dynamic RLS example that I explained in this post, does not include the manager level access. Sometimes you need the manager to have access to all data. This current post explains how to do it;
Sample Dataset
To create a scenario with manager level access, and employee level access, I created two tables as below;
Sales Rep Table. This table has a field which is “Is Manager”, values are zero or one. If the value is one, then the sales rep is a manager and can see everything if the value is zero, then sales rep should be able to see his/her only data rows.
We also have a sales transactions table, which includes all transactions. there is a field in this table which is the link to Sales Rep.
Relationship of these two tables are based on Sales Rep and ID field obviously
Creating the Role
As you can see in the data table we can easily identify which sales transactions belongs to which sales rep. So a role logic to get only rows for every sales rep can be easily implemented with a DAX filter like this:
'Sales Rep'[Email]=Username()
I have explained that method previously in details here. However, that method does not work when I have a “manager” level access too. For a manager level access, we can make some modifications. There are multiple ways of implementing it. This is one way of doing that;
The First Step; Identify the User
The very first step is always identifying who is the person logged into the report in Power BI Service. This can be done with Username() or UserPrincipalName() functions in DAX.
The Secon Step; Is the Logged In User, Manager or Not?
We can use a DAX expression to identify is the person logged in, a manager or not. This can be done with a simple MAXX expression as below;
MaxX( Filter( 'Sales Rep', 'Sales Rep'[Email]=Username() ) ,'Sales Rep'[Is Manager] )
In the expression above, we are using FILTER() to identify all rows from the sales rep table, where the email address matches the logged in user. Then we get the maximum [Is Manager] value from that using MAXX() function. if the result of the expression above is 1, then the person is a manager, otherwise not.
If the User is not Manager, show only records related to the user
If the user is not a manager, then we just show the data related to him/her. this can be an expression as below;
'Sales Rep'[Email]=Username()
If the user is a manager, then show everything
an easy way of showing everything is writing a DAX expression that always returns true as a result. as simple as this;
1=1
All in One
Now if we combine all these codes and logic together, we end up with an expression as below;
If( MaxX( Filter( 'Sales Rep', 'Sales Rep'[Email]=Username()) ,'Sales Rep'[Is Manager])=0, 'Sales Rep'[Email]=Username(), 1=1 )
The expression above will show everything to the manager, and will only show related data to non-manager users.
You can create a role in Power BI under Sales Rep table with the expression above;
Test the Result
After creating this role, publish the report into Power BI, Go to Security configuration of the dataset;
Add all users to the role. There will be no harm for this. If the user is not in your Sales Rep list, they will not see anything. If they are, they will have restricted access.
Then share the dashboard also to all users.
This is what Reza (Restricted user which is not manager) will see;
And this is what Mark (Manager user) will see;
Summary
In summary, this was an add-in to the row level security post. In this post, you’ve learned how you can implement a dynamic row-level security with a manager level access. This method implemented very simply, there are other ways of implementing it as well. In the future, I’ll write about other scenarios of RLS with multiple user profiles as well. If your Row Level Security requirement is different, please let me know in the comments, I would love to know about it, and can help you to implement it.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
Great post again, Reza.
Thank you.
Like the approach! Will give it a try at work.
Thanks Ivan 🙂
Thanks for the good info, Reza. This works great for a two level hierarchy.
We had a different situation where management is multi-level. In other words
Exec1 is over Manager1 who is over Emp1,Emp2,Emp3
Exec1 is over Manager2 who is over Emp4,Emp5,Emp6
When Exec1 is logged in he/she sees everything. When Manager1 is logged in they see only info for 1,2,3; and when Emp1 is logged in they see only their information.
To accomplish this I used PATH() to create the logical path of management (calculated column with the path to the Exec, i.e. “Exec1|Manager1|Emp3”) Then in the roles definition I say User() = PATHCONTAINS(…). This will return the record if the logged in user is in the path column. Works brilliantly.
So now the curveball – sales by Writer (inside salesperson). How can I utilize the same logic simultaneously for Writer vs Salesperson?
My facSales table relates to the dimCustomer table via CustomerID column
My dimCustomer table relates to the dimUser table via the SalespersonID column
For Salespeople/Manager/Exec everything works great… but…
I need to have another active relationship from facSales to dimUser on WriterID. Of course PowerBI will not allow this. Right now when a Writer logs in, they see nothing (because there is no active relationship to the dimUser table…). Do you think it’s possible to use the same dynamic logic and be able to see when logged in as a Writer? Full disclosure – I have not tried any IF statements in the logic, yet 🙂
It should be possible. bottom line worst case we can always look into VLookup functions, but I believe there should be better ways to do this as well.
I’ll work on this for next few posts.
Cheers
Reza
Hi Tad,
Thanks for your comment.
This is actually subject of my blog post next week 🙂 I’ll elaborate that too. Thanks for sharing your scenario here. I believe this is one of the most common scenarios
Cheers
Reza
Hello and thank you.
I tried to replicate the last all-in-one expression/measure, but I receive an error.
A single value for column ‘UserName’ in table ‘Manager’ cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Measure2 = If(
MaxX(
Filter(
‘Manager’,
‘Manager'[UserName]=Username())
,’Manager'[Is Manager])=0,
‘Manager'[UserName]=Username(),
1=1
)
Per my earlier post, I was able to correct the error by inserting another MAX function to produce a single value.
FinalManagerTest = If(
MaxX(
Filter(
‘Manager’,
‘Manager'[UserName]=Username())
,’Manager'[Is Manager])=0,
MAX(‘Manager'[UserName])=Username(),
1=1
)
Hi Michael,
I don’t get that error. However, if you get it, then FirstNonBlank can be a good function to use, MAX might get some weird results with text. It seems worked for your case though!
Cheers
Reza
Reza this is great ! Thanks for sharing this . How to achieve something in which sales rep can see only his data (Like what you showed) however manager should be able to see data only for his sales rep NOT other managers sales reps data, and then others like VP or directors can see everything.,… and since the data is getting filtered in the data model itself is it important to add the users in security on Power BI Service ?
Hi Shariq,
This is what I’m going to reveal in one of the posts next week. RLS based on a organization hierarchy. stay tuned for that.
Cheers
Reza
Is there any method to do Column Level Security. Which mean when certain users logged in I do not want to show some columns for an example I do have inventory qty and price for one type i can show inventory and qty but other group only should see the qty no amount how to address this scenario without creating two reports.
Hi Pathirana,
unfortunately we don’t have column level security yet in Power BI. However, there are some workarounds, such as creating a measure and work on a DAX logic based on RLS. I’ll write about this soon. Thanks for sharing your scenario.
Cheers
Reza
Interesting applying the filter based on DAX in RLS. I think the missing piece is defining a default Role that any logged in User gets evaluated as. Currently you would still need to manually add all users into your User role for RLS. This is dynamic in that a different logged in user gets to see transactions relating to their filtered data. Next level is that any logged in user, regardless of whether they have have been added to RLS has the filter applied. Saves many hours of maintaining users in RLS.
Hi jay,
Thanks for your comment.
One way to fix that can be assigning a Group for everyone. then adding everyone in the company under that group, and assigning the group to the role.
Cheers
Reza
I see this is over a year old but I have a question along the same lines. I’ll try to briefly state this. I have a table of products names A to Z. Some users can see all the Products, some can NOT X, Y and Z but can see all the rest. I have a role created both conditions. How can this be accomplished? My thought is to create a user table that lists USERNAME() with the associated role I want the user to have. How can I assign the user to the role?
Hi,
You can do that with having users and profiles table. check my article about it here.
Thanks for the article. I replicated the procedure but then received an error “MAXX is not allowed as part of the row level security expression on direct query models.”
Any idea of getting around this?
It is working on my example. However, this is only one way of achieving this. there are many other DAX expressions that you can write and achieve the same thing. Another approach is using the LookupValue function. I will update this blog post later with that script too.
Cheers
Reza
Hi there, I just tried this and when I test against my two users, one a manager and one not a manager, neither brings back any results. My Name and field names are slightly different
If(
MaxX(
Filter(
‘DimEmployee’,
‘DimEmployee'[EmailAddress] = USERNAME())
,’DimEmployee'[isManager])=0,
‘DimEmployee'[EmailAddress] = USERNAME(),
1=1
)
but I cant see why this wouldn’t work based on your information
Hi Debbie,
You can also try the LookupValue method like this:
if(
LOOKUPVALUE(‘Sales Rep'[Is Manager],’Sales Rep'[Email],USERPRINCIPALNAME())=1,
true(),
‘DimEmployee'[EmailAddress] = USERNAME()
)
Thank you for the blog.
In my case, i have multiple entries of employee names in a row with a comma as a delimiter(people can view the entry). How can i write the DAX formula for the role to search the name for every entry in the row and then provide the security rule.
Hi Akhila
Why do you have multiple employee names in a row separated with a comma? did you use PATH functions in DAX to get the organizational hierarchy? or this is how it comes as the dataset?
First I suggest to clean up your dataset, you can use a split column by a delimiter in Power Query to have only one employee per row.
if the scenario you are implementing, however, is an organization hierarchy and row level security based on that, read the explanation about that method here.
Hi
I applied RLS in my report successfully but I’d like to add “Default Value” for each RLS.
for example: – Manager1 can see data for Country A, B —> Default country selected in slicer is A
– Manager2 can see data for B, D —- > Default country selected in slicer is B
I appreciate to help me if there is a way to do it in power BI.
Thanks
Hi.
At the moment, you cannot set the default selection of slicer based on RLS. However, you can combine your visuals with DAX to achieve that as a workaround.
Cheers
Reza
Hi,
I’ve successfully been able to apply this process for one level of our org (regional manager), however, I’m struggling to figure out how to do this for our territories. In my situation I’m trying to filter accounts down to the territory manager (TM). The problem is that each amount has two territory managers based on the business unit. I can get the regional filter to work as it’s only one person per account. The two reps:one account seems to keep breaking the logic though and I can’t seem to get around it. I can’t build relationship tables as they won’t allow two relationships (2 reps against a table of all reps in a single column). Any assistance would be awesome, thank you!
Hi Pepper
you need to read my article about users and profiles with RLS.
Cheers
Reza
Thank ;you so much Reza, i have a question, how do i configure rls for people with multiple managers? i tried PATH and i get “duplicate” errors
thanks
If it is one or two more managers, you can have other fields in your dataset with 2nd manager or 3rd manager id, and duplicate this logic for them.
if it is more than that, you need to combine parent-child scenario with many-to-many relationship, which I explained an example of that here.
Cheers
Reza
Hi Reza ,all your posts are great ,I have different requirement in RLS,i have these many Dim fields in main Table/Fact Table say Region,Business Unit,Business Segment and Business Subsegment and in another table or Excel i have EMAIL ID ,Region,Business Unit,Business Segment and Business Subsegment information ,differnt users have different access levels like one has only Region the other has Region,Business Unit and last one has restriction on all fields .how to achive this in PBI RLS ?
You need to create a hierarchy of region/business unit/business segment. Then you can relate users to it and apply RLS on it. I explained similar thing here.
Cheers
Reza
Hi Reza,
Both my dax in RLS says that the RLS-statements defined in the table are not defined as the type true/false. I have tried both with the same result:
IF(LOOKUPVALUE(
‘Sales rep'[IsManager],’Sales rep'[Mail],USERPRINCIPALNAME()) = 1,1=1,USERPRINCIPALNAME())
and
IF(
MAXX(
FILTER(
‘Sales rep’,
‘Sales rep'[Mail] = USERPRINCIPALNAME()),
‘Sales rep'[IsManager]) = 0,
USERPRINCIPALNAME(),
1=1)
Thanks,
Mikkel
Hi Mikkel
where did you write this expression? if the expression is written in a DAX measure you are most probably getting that error. but if it is written in the RLS script window, should work. If not, I’d say we need to look at the data model
Cheers
Reza
Good article. Any idea about if Salesman can see his report beside regional and companywide. Means he cannot see other salesman figures but can see combined figures.
Hi Syed.
Check out my recent blog article about it here.
Cheers
Reza
I need to send different reports to different people a mentioning their territory.
As if Name 1 logins, a territory is assigned to him in the dataset and he can only see dashboard details according to that.
Hi Shagun
You can do that either with static row level security, or with the dynamic one mentioned here.
if you used the dynamic approach, you should have this data in tables, and their relationships. if you use the static, then in the website is where you assign roles to users.
Cheers
Reza
Really enjoyed your article as its highly informative
Thanks for the article, I have implemented RLS in similar way. But for full access users (who don’t have pro license) still not able to see full data like some filter from RLS is being applied.
if(
LOOKUPVALUE(‘RLS Table'[Full Access],’RLS Table'[ID],USERPRINCIPALNAME())=1,
true(),
‘RLS Table'[ID] = USERPRINCIPALNAME()
)
From total of 161 Codes users only see 147 Codes for whom I have kept Full Access column value as 1.
But pro license users see full 161 codes.
Hi Amit
please share more information about your implementation:
how the report is shared with others? worskapce/app? what is the sharing access that users have. how the table structure and the data of that is?
I can then look into this and see what is the problem.
I need to grant access to a dashboard based on staff level. A user should be able to see their data as well as staff levels below them. For example: A Manager can see Senior level data from group A his direct team and group B Senior level data not is direct team.
Hi Peace
read my article here that I explained about it.
Cheers
Reza