Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anktaggrwl
Helper II
Helper II

Implementing RLS to account for dual roles - possible?

 

 

 

I'm creating several dashboards from the same dataset - one dashboard is meant to be a manager's view of his/her team, and another dashboard is meant to be an individual person's view of their relevant data. Managers are also individual contributors, so we would want managers to not only have access to the information about the team they manage, but also for those managers to be able to use the 'individual' version of a dashboard to track their own progress.

 

I have a security table set up, and use the userprinicipalname() expression to limit visibility on the individual level using the following structure:

 

 usertable.PNG

 

While it's probably irrelevant since rolling up isn't the issue - every manager is only concerned with the data of the individuals in the leftmost column - i.e. Keith would not need to know/see information rolled up under Sue or Eloise at their respective manager levels, Keith would be interested in data at each Team Member level. Same goes for Sue only needing to see John, Eloise, Herself, Alice, and Connor at the individual level (not at the Eloise as team lead level that rolls up her team).

 

For individuals like John, Connor, Victoria, and Alice - I've assigned them to the "Team Member" role, where userprincipalname = userprincipalname() - they would only have access to data that is relevant to just them and they won't be given access to the manager dashboard. The individual dashboard is driven by a set of reports that are created to show granular information at an individual  contributor level.

 

For Eloise - she has been assigned to a Team Lead Role, since she can see not only her own data, but also John and Connor's information she has been assigned to a role where TeamLeadUPN= userprincipalname().

I want her to be able to use the same individual dashboard that has been given to John and Connor, but have it only show her own information, while the manager dashboard will be driven by a 'manager' report showing sales amount grouped by team member at a higher level than what John and Connor as individuals see (since this dashboard includes not only John and Connor's info, but Eloise's too.

 

Sue can see her own team and Eloise's team and is therefore assigned to RM UPN= userprincipalname()

 

Keith is assigned to a role where GM UPN= userprincipalname, and so on and so forth.

 

The issue I'm having trouble solving for is that when any manager logs in to the individual version of a dashboard, data is shown at the aggregated team level - so Eloise would see sales amount for not only herself, but for her whole team, Keith would see figures at the whole group level, etc.

 

I've tried creating filter for "Me" vs. "Reports to Me" by using a measure to define something to the effect of [Individual Check] = If(Table1[userprincipalname])=userprincipalname(), TRUE, FALSE) and then a corresponding calculated column to return "Me" where  [Individual Check] = TRUE else return "Reports to Me" but we can't use username/upn expressions in calculated columns.

 

I've also tried used helper columns to add roles/titles to each person and tried to make a calculated column to say something like:

TeamTest =

     If( Table1 [GM Title]="Group Manager",

          'Table 1 [GM Status],

              If(‘Table1'[RM Title]="Regional Manager",

                       'Table1’[RM Status]),

                              If(….so on and so forth down the hierarchy)

 

But that doesn’t work as expected.

 

I’ve also just placed slicers/filters that allow someone to filter at the appropriate managerial hierarchy status – i.e. there is a filter for [GM Status], [RM Status], [Team Lead Status], etc….while in principal, this works for the GM to choose the options in [GM Status], the issue is that the same report cannot be used by Sue, since for her, if the report is filtered to 'Me' at the GM level, she wouldn't see anything and she would need to filter on [RM Status] = "Me" and remove the filter in place for the GM and so on and so forth down to the individual level.

 

From a UX standpoint, I want to try to avoid users clicking on and off filters each time they want to view a report. From a dashboarding standpoint – if I apply a filter to the GM on [GM Status] = “Me” and pin the visual to the ‘individual dashboard’ the same visual will not work for Sue when she accesses the individual dashboard, or for John or Connor or anyone else below the GM – the only way I can think to do this is that the report would have to be filtered at the RM Status level, then pinned to a separate individual dashboard made just for that role, and filtered again at the individual level.

 

I’ve also tried to create the a column at the most basic individual team member level and given everyone the value of “Me” so that when an individual logs in, they see it filtered to “Me” but the issue is when a manager views the report, filtering for “Me” returns not only the manager's individual data, but also everyone the manager oversees.

 

Assigning a GM to userprincipalname=userprincipalname() doesn't seem like a solution since then the GM would only see his own information and no one else's when logging in.

 

While in theory we could have two copies of the same dataset to power the relevant reports (one copy of the of dataset that would have everyone assigned to the Team Member role and one copy of the dataset where managers are assigned to appropriate management roles) – if you multiply this by 8-10 different datasets powering multiple reports each – it would mean we have 16-20 datasets and multiple copies of reports where the only difference between dataset and report pairs would be role assignments.

 

What I want is one individual dashboard, powered by a individual level report where anyone who views it, no matter your managerial level, you see information about YOU.

 

That same dataset and information used at the individual level is used for ‘manager’ level reports on the manager dashboard. Any manager would see sales amount for every individual team member including themselves on the manager dashboard.

 

Ultimately, it would be ideal to have 1 report driving both manager and individual dashboards, where the filter and/or slicer option available to any manager would be “Me” and “My Team” while individuals would only see the option for “Me” – is there any way this is possible that anyone can think of? I would then filter on “Me” to pin visuals to the individual dashboard, and then filter on “My Team” to create a manager level dashboard only available to managers.

 

EDIT: Other thought I've had, but unsure if it would work would be to give the GM security role the rule of userprincipalname=userprincipalname() || GM UPN=userprincipalname() - and then for an individually focused report, have the GM defaulted into the first argument, and if viewing a manager level report/dashboard, the GM would be defaulted into the second half of the OR expression... don't think that's possible... but don't know what else to do.

1 ACCEPTED SOLUTION

Wanted to follow up to let folks know I found a perfect solution to my problem, and now am able to have one report power both a manager and an individual dashboard - and the solution was to restructure my usertable completely.

 

Instead of having a structure like this:

 

usertable.PNG

 

 

 

 

 

I opted to flip the table and have a 1-1 relationship with each Team Member and any and all managers above them, including a line for a manager to his/herself... so the new structure is more like this:

 

 

usertable2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

and the only rule I have in RLS is ViewerUPN=userprincipalname() and everyone in [Viewer] column is placed into that role.

 

The Team Member UPN column drives a fact table that has data related to each team member, so the team member column is the many side of a many-to-one relationship which then controls access to data in the rest of the data model.

 

Then, my reports all have a slicer that have slicer buttons like so:

 

Slicer.PNG

 

 When I pin any visual to the individual level dashboard, I make sure the report is filtered on "Me" before pinning, thereby always pinning individual level data, but when saving the report, I make sure to clear the slicer and report filter, showing everyone on the team's data - what this allows for is that if any individual logs into the individual level dashboard, data shown is only for his/herself - i.e. Keith only sees Keith's data, Eloise sees only Eloise's data, John only see's John's data, BUT, when clicking on a dashboard tile to get more details from a report, when John is taken to the report, his only option in the slicer is "Me" since he has no association with anyone other than himself between the viewer and team member columns.

 

For Keith, when he clicks on "Me" he sees just his own data on the report, but when he clicks on "Reports to Me" he sees data for every Team Member that rolls up into him.

 

Using the same report then, I place a filter for 'reports to me' on the report, and then pin those visuals to the manager level dashboard, but again, I don't save the report with any filter in place. This dashboard is only shared with any manager that has someone reporting to them, but it leads back to the same report as the individual level ones. I could create custom url's for each report and append the filter into the URL to force "Reports to Me" to be selected when originating from the manager dashboard, or "Me" when originating from the individual dashboard, but I haven't gotten around to it yet...i'm just excited I can streamline report and dashboard creation for multiple management levels, control security in a very easy to manage way, and deliver a really easy intuitive UX - and all it took was restructuring how my security table was set up!

 

If anyone wants a demo of the functionality I created - let me know! I'll see if I can mock up a personal dashboard that gives you a sense of how you can use one report for multiple audiences and share it out to folks.

View solution in original post

15 REPLIES 15
v-ljerr-msft
Employee
Employee

Hi @anktaggrwl,

What I want is one individual dashboard, powered by a individual level report where anyone who views it, no matter your managerial level, you see information about YOU.

 

That same dataset and information used at the individual level is used for ‘manager’ level reports on the manager dashboard. Any manager would see sales amount for every individual team member including themselves on the manager dashboard.



Based on my test, you should be able to use username/upn expressions to create measures to create a individual level report for all level users in your scenario. 

 

Assume I have table called "Table1" which contain three users data like below.

 

t1.PNG

 

Then I can create the following measures, and show them on the report, or use some of them as visual level filters to show data at individual level.

CurrentUPN = USERPRINCIPALNAME()
IsCurrentUPN = IF(FIRSTNONBLANK(Table1[UPN],1)=[CurrentUPN],1,0)
Total Value = CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[UPN]=[CurrentUPN]))

Following is final result with account "admin@xxx.onmicrosoft.com".

 

r6.PNG

 

Regards

Thanks for the response @v-ljerr-msft - I had considered going the measures route, but that would imply that for every measure in all of my datasets, I would need to have a duplicated version that is not filtered based on upn, correct? or create a duplicated dataset for all datasets we'll plan to use?

 

If we take your table1 example, and add a [managerupn] column for which the first 2 values are jay's email address, which creates the association with [upn]=Admin's email address and with [upn]=jay's email address and then have a Manager role in RLS defined as [managerupn]=userprincipalname() and Jay is assigned to that role, while "admin" is a team member under Jay, so admin is added to a team member role where [UPN]=userprincipalname() - your example measure below would return false when Jay logs in, because firstnonblank value would be "admin" and therefore he would not see his individual info... 

It seems in your solution, you would still need to have a role definition for Jay placed onto the [UPN] column, so Jay would need to be placed into the [UPN]=userprincipalname() role for that solution to work, meaning Jay couldn't then see admin's data unless it was in a duplicated dataset, correct?

 

 

I think i'm getting closer to an "OK" solution - and see how something like a measure like "IsCurrentUPN" could work and take into account various user roles in RLS - but I'm not sure this will really work on the report authoring or user side as cleanly as I'd like, and therefore might still not really work as a true solution that we will implement...

 

I modified the statement slightly to

 

IsCurrentUPN =
IF (
    CONTAINS ( 'Table1, 'Table1'[UPN], [CurrentUPN] ),
    "Me",
    "My Team"
)

That way if the user is logging in under a GM Role, where their role is defined by a related column [GMUPN]=userprincipalname(), the [UPN] column will be filtered down to only those people that the GM can see, and instead of applying the filter on every measure in my dataset, I could apply a filter to every visual in a report where IsCurrentUPN="Me" since each visual could have multiple measures.

 

 

But that still means that I would need a separate report for a manager who wants to view his her/own information vs. a report where the manager wants to view their whole team, because I won't expect managers to remove the measure-based filter on every visual to then see their team view...which then also means two sets of updates/changes/modifications for every report over time, which then gets back to - do we just have a duplicate dataset for all datasets where we copy measures/data rereshes/queries back and forth each time something needs to change, and then have RLS rules just for individuals on one copy of the dataset, and rls rules for managers on the other copy....?

 

Is there any way to propogate a measure-based filter to the entire report or even to a report page that anyone knows of? At least then, both from a development standpoint and a user standpoint, they can remove and add the filter in one place, instead of every separate visual because as it stands, again, over a large number of reports, if we have to have an individual user version vs. a manager user version of each report, having to apply the 'iscurrentUPN' measure as a filter to EVERY visual on a individual level report (if not every measure) just doesn't seem like the most sustainable solution...

Hi @anktaggrwl,


Is there any way to propogate a measure-based filter to the entire report or even to a report page that anyone knows of? At least then, both from a development standpoint and a user standpoint, they can remove and add the filter in one place, instead of every separate visual because as it stands, again, over a large number of reports, if we have to have an individual user version vs. a manager user version of each report, having to apply the 'iscurrentUPN' measure as a filter to EVERY visual on a individual level report (if not every measure) just doesn't seem like the most sustainable solution...


I agree with you. However, I don't think there is better solution in your scenario currently. So I would suggest you add it as an idea on Power BI Ideas forum to improve Power BI on this feature. Smiley Happy

 

Regards

Wanted to follow up to let folks know I found a perfect solution to my problem, and now am able to have one report power both a manager and an individual dashboard - and the solution was to restructure my usertable completely.

 

Instead of having a structure like this:

 

usertable.PNG

 

 

 

 

 

I opted to flip the table and have a 1-1 relationship with each Team Member and any and all managers above them, including a line for a manager to his/herself... so the new structure is more like this:

 

 

usertable2.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

and the only rule I have in RLS is ViewerUPN=userprincipalname() and everyone in [Viewer] column is placed into that role.

 

The Team Member UPN column drives a fact table that has data related to each team member, so the team member column is the many side of a many-to-one relationship which then controls access to data in the rest of the data model.

 

Then, my reports all have a slicer that have slicer buttons like so:

 

Slicer.PNG

 

 When I pin any visual to the individual level dashboard, I make sure the report is filtered on "Me" before pinning, thereby always pinning individual level data, but when saving the report, I make sure to clear the slicer and report filter, showing everyone on the team's data - what this allows for is that if any individual logs into the individual level dashboard, data shown is only for his/herself - i.e. Keith only sees Keith's data, Eloise sees only Eloise's data, John only see's John's data, BUT, when clicking on a dashboard tile to get more details from a report, when John is taken to the report, his only option in the slicer is "Me" since he has no association with anyone other than himself between the viewer and team member columns.

 

For Keith, when he clicks on "Me" he sees just his own data on the report, but when he clicks on "Reports to Me" he sees data for every Team Member that rolls up into him.

 

Using the same report then, I place a filter for 'reports to me' on the report, and then pin those visuals to the manager level dashboard, but again, I don't save the report with any filter in place. This dashboard is only shared with any manager that has someone reporting to them, but it leads back to the same report as the individual level ones. I could create custom url's for each report and append the filter into the URL to force "Reports to Me" to be selected when originating from the manager dashboard, or "Me" when originating from the individual dashboard, but I haven't gotten around to it yet...i'm just excited I can streamline report and dashboard creation for multiple management levels, control security in a very easy to manage way, and deliver a really easy intuitive UX - and all it took was restructuring how my security table was set up!

 

If anyone wants a demo of the functionality I created - let me know! I'll see if I can mock up a personal dashboard that gives you a sense of how you can use one report for multiple audiences and share it out to folks.

Hi, I am trying to solve the same issue. Could you attach the mock-up you have created so I could take a look?

Cheers,

Mathilda

@anktaggrwl , thanks for working through this solution. I have a similar issue but I could see this solution being a challenge if you have a fact table with multiple rows per team member that you were trying to connect to the table you proposed in your solution because you'd get a many-to-many relationship. Any ideas on how to avoid this?

 

I was initially putting RLS on my dimension table (DimCourse, with instructors having access to their own data and department heads having access to both their own data and data of the faculty in their department), and I have multiple rows per team member in my fact table because they are teaching multiple students (FactGrades).

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Using-DAX-instead-of-RLS-for-multiple-reports...

@anktaggrwl   I am doing alsmot the exact same reporting Will you be able to share the demo file

 

Thanks

Hi @anktaggrwl,

 

Wondering if you had a demo I could refer to! I am doing alsmot the exact same reporting for my company!

Hi @anktaggrwl,

 

Wondering if you had a demo I could refer to! I am doing alsmot the exact same reporting for my company!

Hi, this is very cool. I have a simular plan on emplimenting multipe user access. Can I have a demo of this?

Hi, this is very cool. I have a simular plan on emplimenting multipe user access. Can I have a demo of this?

This is very good. And can be extended with rows labeled "Has reported to me" if we have to deal with a slowly changing dimension for Employees.

Thanks for the response @v-ljerr-msft - I had considered going the measures route, but that would imply that for every measure in all of my datasets, I would need to have a duplicated version that is not filtered based on upn, correct? or create a duplicated dataset for all datasets we'll plan on having?

 

 

If we take your table1 example, and add a [managerupn] column for which the first to values are "Jay@example.com", which creates the association with [upn]="Admin@example.com" and with [upn]="Jay@example.com" and then have a Manager role in RLS defined as [managerupn]=userprincipalname() and Jay is assigned to that role, while "admin" is a team member under Jay, so admin is added to a team member role where [UPN]=userprincipalname() - your example measure below would return false when Jay logs in, because firstnonblank value would be "admin" and therefore he would not see his individual info... 

It seems in your solution, you would still need to have a role definition for Jay placed onto the [UPN] column, so Jay would need to be placed into the [UPN]=userprincipalname() role for that solution to work, meaning Jay couldn't then see admin's data unless it was in a duplicated dataset, correct?

Thanks for the response @v-ljerr-msft - I had considered going the measures route, but that would imply that for every measure in all of my datasets, I would need to have a duplicated version that is not filtered based on upn, correct? or create a duplicated dataset for all datasets we'll plan on having?

 

 

If we take your table1 example, and add a [managerupn] column for which the first to values are "Jay@example.com", which creates the association with [upn]="Admin@example.com" and with [upn]="Jay@example.com" and then have a Manager role in RLS defined as [managerupn]=userprincipalname() and Jay is assigned to that role, while "admin" is a team member under Jay, so admin is added to a team member role where [UPN]=userprincipalname() - your example measure below would return false when Jay logs in, because firstnonblank value would be "admin" and therefore he would not see his individual info... 

It seems in your solution, you would still need to have a role definition for Jay placed onto the [UPN] column, so Jay would need to be placed into the [UPN]=userprincipalname() role for that solution to work, meaning Jay couldn't then see admin's data unless it was in a duplicated dataset, correct?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.