Forum Discussion
RLS when connecting 2 semantic models
- 1 year ago
For anyone who gets this issue in future, this is how i resolved it.
- Build a relationship between the imported table ‘Security Groups’ and your new data table (you must have a field that can be matched e.g. Project Name
- Set the relationship so the imported table filters your data e.g. ‘Single (Security Groups filters data)
- Create measures so you can use the security data in your RLS in this example I did 3:
- UserAccessLevel = CALCULATE(MAX('Security Groups'[access_level_c]),'Security Groups'[email_address] = USERPRINCIPALNAME())
- UserEmailMatch = IF(MAX('Security Groups'[email_address]) = USERPRINCIPALNAME(), 1, 0)
- UserHasProjectAccess = CALCULATE(MAX('Security Groups'[Project Name]), 'Security Groups'[email_address] = USERPRINCIPALNAME() && 'Security Groups'[access_level_c] = "Project")
- Go to Manage Roles, create a new role against you main table (this should be the one you linked in step 3). Switch to default editor and paste the required RLS. Below aligns with this example
- // Match the email to the logged-in user
'Security Groups'[UserEmailMatch] = 1
||
(
// Allow access if the user has "Business_Unit" access
[UserAccessLevel] = "Business_Unit"
||
(
// If the user has "Project" access, filter by matching project names
[UserAccessLevel] = "Project"
&&
'TF_Transactions'[Beakon Project Name] = [UserHasProjectAccess]
)
)
For anyone who gets this issue in future, this is how i resolved it.
- Build a relationship between the imported table ‘Security Groups’ and your new data table (you must have a field that can be matched e.g. Project Name
- Set the relationship so the imported table filters your data e.g. ‘Single (Security Groups filters data)
- Create measures so you can use the security data in your RLS in this example I did 3:
- UserAccessLevel = CALCULATE(MAX('Security Groups'[access_level_c]),'Security Groups'[email_address] = USERPRINCIPALNAME())
- UserEmailMatch = IF(MAX('Security Groups'[email_address]) = USERPRINCIPALNAME(), 1, 0)
- UserHasProjectAccess = CALCULATE(MAX('Security Groups'[Project Name]), 'Security Groups'[email_address] = USERPRINCIPALNAME() && 'Security Groups'[access_level_c] = "Project")
- Go to Manage Roles, create a new role against you main table (this should be the one you linked in step 3). Switch to default editor and paste the required RLS. Below aligns with this example
- // Match the email to the logged-in user
'Security Groups'[UserEmailMatch] = 1
||
(
// Allow access if the user has "Business_Unit" access
[UserAccessLevel] = "Business_Unit"
||
(
// If the user has "Project" access, filter by matching project names
[UserAccessLevel] = "Project"
&&
'TF_Transactions'[Beakon Project Name] = [UserHasProjectAccess]
)
)