Forum Discussion

ashmitp869's avatar
ashmitp869
Responsive Resident
1 year ago
Solved

Dynamic RLS with User

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's avatar
    Greg_Deckler
    1 year ago

    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.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    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.