RLS on different hierarchy levels

[ray_codex]

Hi,

I have this RLS question that I can't solve. In our organisation, because of complaincy, we want users to be able to see their own submitted hours but not the hours of other users. But, and this makes it difficult, if a user looks at data on a higher level (department, company, costcenter) we want the user to be able to see all data. Users shouldn't be able to see data of other users if they zoom in on a single UserID level. Excluded are managers, they should see all data.

First I created a non-related table in the model, SecurityTable, it looks like this:

UserID	Name	Email	Permissiontype
1	John Doe	johndoe@contoso.com	All
2	Jane Roe	janeroe@contoso.com	Single

I thought about creating a DAX expression as a RLS filter to cover this:

VAR CurrentUser =
    USERPRINCIPALNAME ()
VAR UserRights =
    LOOKUPVALUE ( SecurityTable[Permissiontype], SecurityTable[Email], CurrentUser )
VAR SelectedUserID =
    LOOKUPVALUE ( SecurityTable[userID], SecurityTable[Email], CurrentUser )
RETURN
    IF (
        UserRights = "All",
        TRUE (),
        IF (
            DISTINCTCOUNT ( factHours[userID] ) = 1,
            IF ( factHours[userID] <> SelectedUserID, FALSE (), TRUE () ),
            TRUE ()
        )
    )

This doesn't seem to work if I test it in Desktop. It could have something to do with my logic or I'm trying to do something that's impossible.

Anyone who has any ideas or advice?

[v-csrikanth]

Hi @ray_codex 
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @ray_codex 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @ray_codex 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth · Accepted solution]

Hi @ray_codex 
Sorry for the late response.
Could you try the below suggested that might resolve your issue effectively.
***********************************************************************

VAR CurrentUser = USERPRINCIPALNAME()
VAR UserPermission =
LOOKUPVALUE(SecurityTable[Permissiontype], SecurityTable[Email], CurrentUser)
VAR CurrentUserID =
LOOKUPVALUE(SecurityTable[UserID], SecurityTable[Email], CurrentUser)
VAR DistinctUsersInContext = CALCULATE(DISTINCTCOUNT(factHours[UserID]), ALLSELECTED(factHours))

RETURN
IF (
UserPermission = "All",
TRUE(),
IF (
DistinctUsersInContext = 1,
factHours[UserID] = CurrentUserID,
TRUE()
)
)
***********************************************************************

If the above information helps you, please give us a Kudos and marked the Accept as a solution.

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @ray_codex

Thanks for using the Microsoft Fabric Community.

Based on the requirements outlined, here is an updated DAX formula to meet the compliance requirements effectively.

Users should only see their own submitted hours at the individual User ID.
Users should see all data when viewing aggregated data (e.g., department, company, cost center).
Managers should see all data without restriction.
DAX Expression for your RLS filter:

VAR CurrentUser = LOWER(USERPRINCIPALNAME())
VAR UserRights = LOOKUPVALUE(SecurityTable[Permissiontype], LOWER(SecurityTable[Email]), CurrentUser)
VAR SelectedUserID = LOOKUPVALUE(SecurityTable[UserID], LOWER(SecurityTable[Email]), CurrentUser)

RETURN
IF(
UserRights = "All",
TRUE(), -- Managers can see all data
IF(
DISTINCTCOUNT(factHours[UserID]) = 1, -- Zoomed into a single UserID
VALUES(factHours[UserID]) = SelectedUserID, -- Allow only if it's the user's own data
TRUE() -- Allow all data at higher aggregation levels
)
)

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Thanks and Regards,
C Srikanth

[ray_codex]

Unfortunately, this doesn't work.