topic Re: RLS-modelling with several roles and filters to access Facts in DAX Commands and Tips

RLS-modelling with several roles and filters to access Facts

Puttsson — Tue, 10 May 2022 05:36:45 GMT

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!

Re: RLS-modelling with several roles and filters to access Facts

Puttsson — Wed, 11 May 2022 09:53:15 GMT

Anyone with some advice/expertise here?

Re: RLS-modelling with several roles and filters to access Facts

Anonymous — Fri, 13 May 2022 02:11:10 GMT

Hi Puttsson ,

Maybe you can create relationships by using measures. Try to use CROSSFILTER.

CROSSFILTER: Specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns.

Please refer to the blog to see if it helps you.

Specifies the cross-filtering direction to be used in a calculation for a relationship that exists between two columns.

If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output.

How to Get Your Question Answered Quickly

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: RLS-modelling with several roles and filters to access Facts

Puttsson — Fri, 13 May 2022 12:28:52 GMT

Thanks for your reply.

I'm not sure how Cross-filtering would help here. Maybe my description is not detailed enough.

I potentially need 4 different people to have access to the same information at the same time.
The Cross-filtering would be on a measure for a specific fact table, and tell the model to use a specific relationship (like RLS), but I want to use all relationships at the same time.

Patrik