Forum Discussion
Implementing RLS to account for dual roles - possible?
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:
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:
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:
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.
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. :smileyhappy:
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:
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:
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:
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.
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.
