Dynamic Row Level Security with Manager Level Access in Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

2017-11-07_18h16_50

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.

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.

2017-11-07_17h53_49

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.

2017-11-07_17h55_03

Relationship of these two tables are based on Sales Rep and ID field obviously

2017-11-07_17h55_53

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:

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;

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;

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;

All in One

Now if we combine all these codes and logic together, we end up with an expression as below;

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;

2017-11-07_18h11_13

Test the Result

After creating this role, publish the report into Power BI, Go to Security configuration of the dataset;

2017-11-07_18h13_16

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.

2017-11-07_18h14_34

Then share the dashboard also to all users.

This is what Reza (Restricted user which is not manager) will see;

2017-11-07_18h15_39

And this is what Mark (Manager user) will see;

2017-11-07_18h16_50

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad

Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.


16 Comments

  • 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 ?

  • 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

Leave a Reply

Your email address will not be published. Required fields are marked *