Forum Discussion
RLS Super User or not
Consider a simple example of say Timesheets. I want to know if it is possible to implement say 2 business rules of security:
1. Logged in user can see their own timesheets
2. A super user can see all timesheets
Say I have 4 tables Timesheets, TimesheetLines and Resources and a SuperUsers table. Timesheets joins to Resources on the TimesheetOwnerId and Resources has an id, name and email address that matches the USERPRINCIPALNAME().
My DAX can be:
Measures:
LoggedInUser = USERPRINCIPALNAME()
LoggerInUserId = LOOKUPVALUE(Resources[ResourceId], Resources[EmailAddress], [LoggedInUser])
RLS on TimesheetLines:
NOT ISBLANK([LoggedInUser]) && (
RELATED(Timesheets[TimesheetOwnerId]) = [LoggedInUserId] //My timesheet
||
CONTAINS(SuperUsers, [ResourceId], [LoggedInUserId]) //Superusers can see Everyone's timesheet
)
This all works fine. The feature I want is to for superusers to highlight which rows belong to them (say conditional formatting to change background colour), so in my head I am thinking I need a flag to indicate if my own timesheet true/false but how do I implement that?
I cannot use USERPRINCIPALNAME() function in a calculated column.
I tried to clone my Timesheets table by referencing it and applying the same RLS DAX obviously excluding superusers however I could not join the 2 tables as then the filter is applied in both directions, and I could not not join because then I could not use RELATED function. So I am thinking I have to use a non relationship method to join from cloned table to the real table then perhaps related would work or something.
Since writing this I discovered a similar issue: https://www.edureka.co/community/29920/level-security-applied-through-lookupvalue-default-filter
But I didn't understand the suggestion in this website - what field do I use in the below and what is my expression.
In my scenario:
Measure =
IF(ISFILTERED(<Field>),
<expression>,
CALCULATE(<expression>,
FILTER(Timesheets, Timesheets[TimesheetOwnerId] = [LoggedInUserResourceId)
)
)Another suggestion I had was
Measure = SELECTEDVALUE(Timesheets[TimesheetOwnerId]) = [LoggedInUserResourceId]but was not sure why this works. Maybe I need to perservere with this.
3 Replies
- amitchandakSuper User
GryPTonite , not very clear. You have created a hierarchy or user access table
RLS - Row Level security
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns-in-Power/td-p/712613
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/
https://radacad.com/what-do-you-need-to-implement-dynamic-row-level-security-in-power-bi- GryPToniteFrequent Visitor
I guess the SuperUsers table is a custom list and acts as access table or rather a list of users that can see all timesheets. Person logged in is either a super user or not.
If a super user logs in he can see everyone's timesheets including his own, in order to diferentiate what is the super user's timesheets and those that are not I want a true/false flag per Timesheetlines (if the flag has to sit in the Timesheets table that is fine because I have a relationship via TimesheetId so I can use the RELATED function. What determines his own timesheet is his parent table Timesheets has a TimesheetOwnerId field that matches the ResourceId in the Resources table and this table has an emailaddress that matches the USERPRINCIPAL(). I suggest you read the link I provided to get a similar example, one person replied to this article suggesting it might be a bug and gave and alternative solution, is the alternative solution viable.
- v-xicaiCommunity Support
Hi GryPTonite ,
You may set Row-Level Security (RLS) to manage roles, please see the link Power BI Desktop Dynamic security cheat sheet, which described the detailed steps. Maybe it doesn't work, there are some tips to let it work and test it efficiently.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.