Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

dynamic RLS

hello I have this table with the accesses that each user must have. furthermore i created this rule, however with use the maxx is only the last value is returned example: [email protected] should h...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Anonymous ,

     

    As the formula in the link you refer to you should create：

     

     

    Org Level 4 = LOOKUPVALUE (
    User[Email],
    User[Name],
    PATHITEM ( 'User'[Hierarchy], 4)
)

Org Level 3 = LOOKUPVALUE (
    User[Email],
    User[Name],
    PATHITEM ( 'User'[Hierarchy], 3)
)

Org Level 2 = LOOKUPVALUE (
    User[Email],
    User[Name],
    PATHITEM ( 'User'[Hierarchy], 2)
)

Org Level 1 = LOOKUPVALUE (
    User[Email],
    User[Name],
    PATHITEM ( 'User'[Hierarchy], 1)
)

     

     

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

     

    Best Regards,

    Dedmon Dai

Abilaash98's avatar
Abilaash98
Frequent Visitor
3 years ago

Hi Joaoar,

 

I had similar requirement and below is how I solved it.

 

EmailAccess LevelNameHierarchyRole_OrderFirst_RoleSecond_Role
[email protected]AAABBBBBBAAA | BBB1BBB 
[email protected]AAABBBCCCCCCAAA | BBB | CCC1CCC 
[email protected]AAABBBCCCEEEEEEAAA | BBB | CCC | DDD | EEE1EEE 
[email protected]AAABBBCCCFFFFFFAAA | BBB | CCC | FFF2 FFF

 

Role_Order: New column using Power Query editior. (Link: https://youtu.be/7CqXdSEN2k4)

First_Role: Calculated column Dax: If(Role_Order = 1,Name).

Second_Role: Calculated column Dax: If(Role_Order = 2,Name).

 

RLS:

 

PATHCONTAINS(User[Hierarchy],

Maxx(

Filter(

User,

[Email]=USERPRINCIPALNAME()

)

, User[First_Role]

)

) | |

PATHCONTAINS(User[Hierarchy],

Maxx(

Filter(

User,

[Email]=USERPRINCIPALNAME()

)

, User[Second_Role]

)

)

 

Thanks.