Difference in Calculated table and RLS

eliebe-5 — Thu, 17 Oct 2019 10:31:35 GMT

I'm having some trouble with creating an RLS Role for managing which department a user may view employees from.

I have a table of departments built like paths, IE:

Dep1/

Dep1/Site1

Dep1/Site1/Team1

Dep1/Site1/Team2

Dep1/Site2

Dep1/Site2/Team3

Dep1/Site2/Team4

Each row has a manager, which matches ids in my usertable. For a manager to be able to access departments below them I create a calculated column in the departments table so that Column = Manager & ParentManager & GrandParentManager.

So my role filter is: (OrganizationalEntity is department and Solution is my aggregated column)

VAR USERID =
CALCULATE(
 FIRSTNONBLANK( SystemUser[systemuserid], 1),
 FILTER( ALL( SystemUser ), SystemUser[Domain Name] = USERPRINCIPALNAME()
 )
)

RETURN
IF(
IFERROR(SEARCH(USERID, OrganizationalEntity[Solution]), -1) > -1,
TRUE,
FALSE
)

But this simply leaves me with all departments with a nonblank "Solution" column.

If I instead create a calculated table, use the same definition for USERID but swap userprincipalname() for my email, like this:

TestTable = 

VAR USERID =
CALCULATE(
 FIRSTNONBLANK( SystemUser[systemuserid]; 1);
 FILTER( ALL( SystemUser ); SystemUser[Domain Name] = "MYEMAIL@DOMAIN.COM"
 )
)

RETURN
    CALCULATETABLE(
        OrganizationalEntity;
        FILTER(
            OrganizationalEntity;
            IF(
            IFERROR(SEARCH(USERID; OrganizationalEntity[Solution]); -1) > -1;
            TRUE;
            FALSE
            )
        )
    )

This generates a table with expected results.

Is this a bug? Or am I just missing something?

EDIT: I want to add that I have tried simply OrganizationalEntity[Manager] = USERID, and that works as expected in RLS, so USERID seems to be correct.

topic Difference in Calculated table and RLS in DAX Commands and Tips

Difference in Calculated table and RLS