Dynamic RLS with User

[ashmitp869]

Hi All

I tried to make a dynamic RLS per user, but I was unable to make it access according to requirement.

The requriement would be that users 9 or User 1 can access the for respective Project_id - which is table Core_UserProject
User_id 9 should only view the project_id 1 and 5

User_id 7 should only view the project_id  

the dax code I made against the Projects Table :

VAR _Restriction = 
CALCULATETABLE(
    VALUES( Core_UserProject[Project_id]),
    Core_UserProject[Email] = USERPRINCIPALNAME()
)
VAR RLS_Restriction =
SWITCH(
    TRUE(),
    _Restriction = "None", TRUE(),
    Projects[Project_id]=_Restriction,TRUE(),
    FALSE()
)
RETURN
RLS_Restriction

Here are the bases and their respective relationship:

Projects

Core_UserProject

sample file link

https://github.com/suvechha/samplepbi/blob/main/sample_info%20-%20security.pbix

Thanks

[Greg_Deckler · Accepted solution]

@ashmitp869 So, normally you would create a user's table that would have the User_id and Email and you would relate that to your Core_UserProject table. Your Core_UserProject table would be related to your Projects table. So you would configure your RLS rule against your Users table using USERPRINCIPALNAME.

[ashmitp869]

Hi @Anonymous 

@Anonymous 

@v-xul @v-xiaoql @v-xicai @Greg_Deckler @lbendlin Any assistance.
Thanks