Implementing RLS to account for dual roles - possible?

[anktaggrwl]

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:

 

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.