Forum Discussion

ray_codex's avatar
ray_codex
Resolver II
1 year ago
Solved

RLS on different hierarchy levels

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 t...
Need Help
  • v-csrikanth's avatar
    v-csrikanth
    1 year ago

    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's avatar
v-csrikanth
Community Support
1 year ago

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's avatar
ray_codex
Resolver II
1 year ago

Unfortunately, this doesn't work.