RLS Dynamic with Multiple Users

[hugo_barbara]

Hey guys!

I tried to make a dynamic RLS per access level, but I was unable to make the first level able to access all the information.

The context would be that level 1 users can access all information, level 2 will only access data from their region and level 3 will only be able to access the region and its respective sector.

When creating the dax code I made this example below:

VAR LevelAcess = LOOKUPVALUE(dUser[Role], dUser[User], USERPRINCIPALNAME())

VAR RegionUser = LOOKUPVALUE(dUser[Region], dUser[User], USERPRINCIPALNAME())

VAR SectorUser = LOOKUPVALUE(dUser[Sector], dUser[User], USERPRINCIPALNAME())

RETURN

IF (

    LevelAcess = 1,

    TRUE(),

    IF(

        LevelAcess = 2,

        [Region] = RegionUser,

        IF (

            LevelAcess = 3,

            [Region] = RegionUser && [Sector] = SectorUser,  

            FALSE()

        )

    )

)

Here are the bases and their respective relationship:

[hugo_barbara]

@mark_endicott 

Thank you very much!! When following the steps provided, I was able to see the error.

Thank you very much!

[mark_endicott · Accepted solution]

@hugo_barbara - There are a few things here.

1) Check on which table your rule is applied - The way your DAX is written it should be applied to the 'fVendas' table. 

2) If this is were your DAX rule is being used, then you should not use relationships, because the LOOKUP will find the relevant data from dUser and apply it to fVendas through a virtual relationship. 

3) In particular the bi-directional nature of one of your relationships will be causing the RLS to fail. 

4) The inactive relationship can remain, but it may cause you errors or failing RLS when the particular measure that 'activates' it is used. 

If this helps, please accept it as the solution to help others with the same challenge.