RLS with parent child hierarchy

[Lebombaa]

Hello!

I want to implement RLS from a hierarchy path calculated by the path function. Hierarchy is a column calculated with the path function and OrgID is the child node an dcontains letters and numbers.

this is my DAX code in the Dax editor for RLS.

PATHCONTAINS(

    Accesstree[Hierarchy],

    MaxX(

        Filter(

            Accesstree,

            [Email] = USERPRINCIPALNAME()

        ),

        Accesstree[OrgID]

    )

)

The problem here is when I assign this role and log in with an email, it misses some parts because people can have an email linked to several OrgIDs, and then I only see the OrgID that has the highest value because of MaxX function. How can I rewrite the code to fix this? When peolpe log in, they should be able to see all of the rows with their own OrgID and the ones that are under them in the hierarchy/path.

[OwenAuger · Accepted solution]

Hi there @Lebombaa 

Something like this should do the trick:

VAR UPN = USERPRINCIPALNAME()
VAR CurrentUserOrgID =
    CALCULATETABLE (
        SELECTCOLUMNS ( VALUES ( Accesstree[OrgID] ), "@CurrentUserOrgID", Accesstree[OrgID] ),
        Accesstree[Email] = UPN,
        REMOVEFILTERS ()
    )
VAR CurrentHierarchy =
    Accesstree[Hierarchy]
RETURN
    NOT ISEMPTY (
        FILTER (
        CurrentUserOrgID,
            PATHCONTAINS(
                CurrentHierarchy,
                [@CurrentUserOrgID]
            )
    )
)

The basic idea is that CurrentUserOrgID is a table of all OrgID values for the current user. Then we check whether the current row's path contains any of those, and if so return TRUE, otherwise FALSE.

Does this work for you?

[Lebombaa]

Yes it worked thank you!