Different RLS for different reports using the same semantic model

[ameldrum]

Hello,

I have a semantic model that powers 6 different reports.  I have RLS set up for different levels of our Org that work correctely.  However, one of our top levels needs a different view depending on the report.  For one report, they want to see it filtered by zone, and for another report, they want to only see their directs as outlined in the Provider_Dim.  Below is diagram of my model

Report 1 needs to filter Provider_Dim on Userprincipalname() and Report 2 needs to filter Zone_Dim by Userprincipalname(). (zone_dim contains an email column that is not shown).  
Things I have tried:

I created an unlinked table with the names of each of my reports, then added that column to the Filter all pages on the filter pane and selected just the name of the report it was then locked and hid it so it wouldn't change.  I then wrote this DAX in the RLS for this Usergroup under the Provider_Dim table.

-- Find Email of logged in user
VAR LoggedInUser = USERPRINCIPALNAME()
-- Captures active report
VAR ActiveReport = SELECTEDVALUE('Report_Identity_RLS'[ReportName]) 

RETURN
-- If the report filters by Heirarchy, apply Individual-based RLS
IF(    
    ActiveReport = "Report 1",
    [RLS_EmailAddress] = LoggedInUser,
    TRUE() -- Default: Allow all rows (no filtering)
)

 I did the same for Zone_Dim but changed some of the variables accordingly.

This unfortunatly didn't work because SELECTEDVALUE doesn't compute properly in RLS and I have not found a way to identify which report is active to apply dynamic RLS.

I tried creating a column that would mark what rows were needed based on userprincipalname so I could apply the filter at the report level but userprincipalname is not allowed in column creation as it is calculated at refresh not at user log in.

I tried creating a slicer that I could hide and sync across all the pages, but I haven't been able to set the default to the UPN and it can be wiped out by the Reset Filter button.

I have tried to create a table that mixes together the zone and provider emails in a way that all is selected but then on the individual report I can indicate whether it be filtered for Zone or Provider but since they are both different fields, I don't know how I would make it come together.

I tried creating a RLS table that linked to both Provider_Dim and Zone_Dim but both relationships could not be active and I don't want to write complicated measures for everything in each report to use one relationship or the other.  I couldn't find a way to activate a relationship report wide.

Please let me know if there is any trick or way to accomplish this!  Thank you in advance!

[lbendlin]

If you have sufficiently different audiences then you better use separate reports or even workspaces.

[ameldrum]

Its the same audience, just a different view based on the report.  Same user will want to see Zone level detail on Report 2 and Person level detail on Report 1.  Separating the reports/semantic models wont help exactly because there are other groups that view these reports and their access is just fine.  Its just this one security group that needs different views per report that is the issue. We also don't want to separate them for maintenance and refresh schedules.

[ArwaAldoud]

You can create a table that maps users to specific reports and their filters e.g UserPrincipalName, reportName, filterType

use LOOKUPVALUE() instead of SELECTEDVALUE() to get the report filter for the logged-in user example DAX for Provider_dim:

var loggedInUser = USERPRINCIPALNAME()
var reportFilter = LOOKUPVALUE('SecurityTable'[FilterType], 'SecurityTable'[User], loggedInUser)
Return IF(reportFilter = "Provider", [RLS_EmailAddress] = loggedInUser, TRUE())

You can use a bridge table to manage relationships and toggle between Provider_Dim and Zone_Dim. Use USERELATIONSHIP() in DAX to switch relationships dynamically.

Let me know if this helps
If this response was helpful, please accept it as a solution and give kudos to help other community members

[ameldrum]

Thank you for your response.  I may not fully understand your solution but here is where I am still having issues. 

If I create a table with all the users and mark which report it would look like this 

there would be multiple entrys for each person because the same person needs a different view between reports. I don't see how Lookupvalue() would help when it is like that because it still doesn't tell me which which report they are viewing and doesn't apply the different rules.

When I tried the bridge table, the main issue is that there are 2 distinct filter possibilities; everyone (including clients) in a zone, or, just a subset of providers.  I cant just join on provider ID as when it requires zone level, it wont bring in all the date for the zones and if I join on zone it will bring too much info when it should be a smaller subset.  As for switching between the relationships and duplicating every measure, that just isn't realistic given the size of the reports.

I appreciate the reply, if I am missunderstanding your solution, please correct me as I would like to get this to work.