Forum Discussion
RLS when connecting 2 semantic models
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]
)
)
Hi KathyGG - RLS doesn’t automatically transfer between datasets, and direct joins in composite models don’t inherently carry RLS across.
When you connect to your primary dataset (Transformed Data) in the Timesheet Data model, make sure you’re using DirectQuery for Power BI datasets. This allows you to create a composite model with both datasets, and RLS will only apply to tables from the Transformed Data dataset. You won’t be able to modify RLS from within the Timesheet Data report, but you can still leverage it.
If direct RLS transfer isn’t possible, you could consider a workaround by creating report-level filters based on the users' access levels. Here’s how:
Add a calculated column or measure that checks the user’s identity (e.g., with USERPRINCIPALNAME()) and filters data according to the RLS rules defined in the Transformed Data model.
Then apply this calculated column/measure in the Timesheet Data model as a filter.
If the data volumes and architecture allow, consider consolidating both datasets into one model (Transformed Data) to apply RLS seamlessly across all tables. This way, you avoid complications with cross-model RLS and can apply roles directly on all relevant tables.
Role Setup in Power BI Service: Sometimes, RLS fails in the Service if the dataset permissions or workspace settings are not aligned. Verify the following in Power BI Service:
Ensure the users have Viewer role access to the workspace where the dataset is published. Any role higher than Viewer could bypass RLS.
Check that the permissions on both datasets don’t conflict (for example, having RLS on both models independently without a clear hierarchy can cause issues).
Reference link:
Use composite models in Power BI Desktop - Power BI | Microsoft Learn
Hi rajendraongole1 thanks for the response.
I have connected as a DirectQuery and it does appear that my issue is around the RLS only applying to Transformed Data which is not relevant to Timesheet Data (only common field is Project Name).
Can you further explain the userprinciplename filter step you mention? So i create the measure User = userprinciplename() and then how do i use it to apply as a filter?
If it helps, the reason I am trying to link with Transformed Data as this has a Security Groups table which defines the individuals email (to align to userprinciplename) defines their access level = all / project and defines their projects. The issue I need to be careful of, is for some people they have all access so it needs to ignore their project and show them everything. This is the RLS dynamic formula I am currently using in Transformed Data =
[email_address]= USERPRINCIPALNAME() || MAXX(FILTER('Users Register', 'Users Register'[access_level_c] = "Business_Unit" && [email_address]= USERPRINCIPALNAME() ), [access_level_c]) = "Business_Unit"
Unfortunately I can't add Timesheet data into Transformed Data as it has different credentials and access permissions which would hinder updates to Transformed Data which we do quite regularly.
Hi KathyGG - To apply dynamic row-level security (RLS) across data sources where only certain fields align, you can use USERPRINCIPALNAME() to filter data by the logged-in user’s email.
UserEmail = USERPRINCIPALNAME()
In the Transformed Data table, define the RLS rule using your logic. To allow for both specific project access and full access, your formula should check the user’s access level, allowing visibility to all projects if they have “all” access.
[email_address] = USERPRINCIPALNAME()
|| MAXX(
FILTER('Users Register',
'Users Register'[access_level_c] = "Business_Unit"
&& [email_address] = USERPRINCIPALNAME()
),
[access_level_c]
) = "Business_Unit"
|| MAXX(
FILTER('Users Register',
'Users Register'[access_level_c] = "All"
&& [email_address] = USERPRINCIPALNAME()
),
[access_level_c]
) = "All"This formula:
Filters data where [email_address] matches USERPRINCIPALNAME() (email match).
Checks if the user has access at a "Business_Unit" level or "All" access.Since the Timesheet Data can’t be directly joined with Transformed Data due to different credentials, implement RLS separately:
In Timesheet Data: Create a filter that matches on the Project Name where possible, and only grants access to projects listed for the user in the Users Register table.
You could use a DAX formula similar to the Transformed Data rule, modified to only apply access based on projects.
This approach leverages dynamic RLS and your existing security groups, ensuring each user’s access is defined by their level and association with specific projects.