Forum Discussion

GryPTonite's avatar
GryPTonite
Frequent Visitor
6 years ago

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