RLS-modelling with several roles and filters to access Facts
Hi, I've been struggeling for a few weeks with my PBI-model. I have a solution that is working today, but it completely killed the performance of the report. So I need to find another way around my issue, and some dynamic RLS might be the way to go. I have a LinkTable which contains LinkTableId to connect to the FactTables. Each LinkTableId has ActivityAdminId, ActivityBrokerId, ResponsibleAdminId, ResponsibleBrokerId (different roles/people/users that are related to the specific row in the facttable). This is 4 different people/users (the same user could have all 4 roles at the same time on the same LinkTableId). What I need is to filter the linktable based on which user is using the report. I've written this RLS-DAX-code on my LinkTable: [ResponsibleBrokerId] IN CALCULATETABLE ( VALUES ( 'AccessTestTable'[BrokerId] ), FILTER ( ALL ( 'AccessTestTable'), [UserPrincipalName] = USERPRINCIPALNAME() ) ) || [ActivityBrokerId] IN CALCULATETABLE ( VALUES ( 'AccessTestTable2'[BrokerId] ), FILTER ( ALL ( 'AccessTestTable2'), [UserPrincipalName] = USERPRINCIPALNAME() ) ) So far all is good. Now in my model, each use can have different type of access. The user should in some context have access to other users. This is today handled with a filter on the whole report (or a specific page / data), which comes from my Access-table: I've been trying to adjust my RLS-DAX to use this information, but so far it doesn't care about my page/report-filter - something like this. It did work if I replace "IN CALC...." with just = 1 (the value, but then it's not dynamic as I want it) Summary of my needs: The user should have access to all linktableids where the user has any of the 4 roles (ActivityAdminId, ActivityBrokerId, ResponsibleAdminId, ResponsibleBrokerId) I need to be able to filter specific reports / pages based on "Mentorsrad" so that a user can only access the data where AccessTestTable[Mentorsrad] = the chosen value I also want to be able to filter so that the user can choose "Now I only want to see rows which I have access to based on role ActivityAdminId" (Any of ActivityAdminId, ActivityBrokerId, ResponsibleAdminId, ResponsibleBrokerId) Todays solution (in case someone can figure something smart out from this) RLS on DimAccess. AccessToLinkTable contains all LinkTableIds each BrokerId should have access to. But this table is like 25m rows, so everything gets super slow. Any help is appreciated! If you need any more details of my model, just ask and I'll try to answer!
