Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

RLS not working consitently

Hello,

 

I'm working on applying RLS on my dashboard and I'm not sure why it doesn't work in some profiles. Here's a sample table of what my employee table looks like.

 

Notes:

  • Added PathContains to test the ideal filter if a certain manager takes a took at the dashboard
  • EMPLOYEE_ID is not unique as there are times an employee moves to a different manager (see sample below)

 

EMPLOYEE_IDNAMEMANAGER_IDMANAGER_NAMEPATHEmailPATHCONTAINS(PATH,"10001")
10001Employee110019Employee010019|10001[email protected]FALSE
10002Employee210001Employee110019|10001|10002[email protected]TRUE
10003Employee210020Employee1910019|10020|10003[email protected]FALSE
10004Employee410020Employee1910019|10020|10004[email protected]FALSE
10005Employee510001Employee110019|10001|10005[email protected]TRUE
10006Employee610020Employee1910019|10020|10006[email protected]FALSE
10007Employee710001Employee110019|10001|10007[email protected]TRUE
10008Employee810001Employee110019|10001|10008[email protected]TRUE
10009Employee910001Employee110019|10001|10009[email protected]TRUE
10010Employee1010001Employee110019|10001|10010[email protected]TRUE
10011Employee1110001Employee110019|10001|10011[email protected]TRUE
10012Employee1210001Employee110019|10001|10012[email protected]TRUE
10013Employee1310001Employee110019|10001|10013[email protected]TRUE
10014Employee1410001Employee110019|10001|10014[email protected]TRUE
10015Employee1510001Employee110019|10001|10015[email protected]TRUE
10016Employee1610001Employee110019|10001|10016[email protected]TRUE
10017Employee1710001Employee110019|10001|10017[email protected]TRUE
10018Employee1810001Employee110019|10001|10018[email protected]TRUE
10020Employee1910019Employee010019|10020[email protected]FALSE

 

This table is connected to different tables that point out their works and performance.

 

Moving along, I have this measure to get their employee ID so that I can run the PATHCONTAINS in RLS:

 

Get_EMPLOYEEID = CONVERT(CALCULATE(MAX(EmployeeDatabase[EMPLOYEE_ID]), LOWER(EmployeeDatabase[Email]) = LOWER(USERPRINCIPALNAME())), STRING)

 

 

And in my Mange Role, I made this one inside the employee table:

 

PATHCONTAINS([PATH], [Get_EMPLOYEEID]) = TRUE()

 

 

This thing works well only if you're not maging people (not a manager) but if you are a manager, it only filters your own data but not the PATH column.

 

My Wishlist:

- I'm not sure how to fix this one, please help. Ideally the filter in PATHCONTAINS(PATH,"10001") should apply.

- Is there a way to dynamically change the value in PATHCONTAINS? I'll use that column to filter what should be filtered.

Data Protection
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

    You can create the rule directly in rls, you can put the following code to rls.

    VAR a =
    CALCULATE (
        MAX ( EmployeeDatabase[EMPLOYEE_ID] ),
        LOWER ( EmployeeDatabase[Email] ) = LOWER ( USERPRINCIPALNAME () ),
        ALLSELECTED ( EmployeeDatabase )
    )
RETURN
    CONTAINSSTRING ( EmployeeDatabase[PATH], a )

    Then test it.

     

    Output

     

    Best Regards!

    Yolo Zhu

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

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

    You can create the rule directly in rls, you can put the following code to rls.

    VAR a =
    CALCULATE (
        MAX ( EmployeeDatabase[EMPLOYEE_ID] ),
        LOWER ( EmployeeDatabase[Email] ) = LOWER ( USERPRINCIPALNAME () ),
        ALLSELECTED ( EmployeeDatabase )
    )
RETURN
    CONTAINSSTRING ( EmployeeDatabase[PATH], a )

    Then test it.

     

    Output

     

    Best Regards!

    Yolo Zhu

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