topic Re: Dynamic RLS with multiple tables in DAX Commands and Tips

Dynamic RLS with multiple tables

debenaire — Tue, 31 Oct 2023 15:00:01 GMT

Hi there. I have a reasonably complicated dynamic RLS which i am trying to resolve through dax. Attached is the schema.

The premise is the Id in the user table is the USERRPINCIPALNAME

The user has a a list of companies they are allowed access to in the USERALLOWED table

These companies have a relevant ID called InstanceId-CRMId that gets passed into a site list and on to the report to limit the data.

The issue i am haivng is i do not want to have to use bidirectional relationships

There iaremany to many relationships

Performance is poor

I cannot seem to get the RLS to properly work and the sites table isnt filtering when I add ID = USERPRINCIALNAME() in the user table.

Can someone suggest specifc dax for this use case please??? I have looked at some RADACAD posts on this example but can't work it out. Thanks in advance!!

Re: Dynamic RLS with multiple tables

MarkLaf — Tue, 31 Oct 2023 17:01:45 GMT

The filter from User will propogate as long as it travels through one --> many relationships from the one side.

As you've noted, it doesn't work nicely when traveling from the other direction, many --> one, like UserAllowedCompany (many) --> (one) Company.

To get this working without relying on bidirection filtering, you'll need to define an RLS filter rule on Company. Note that once that is working, it looks like the RLS filter will propogate throughout the rest of your model through one --> many relationships.

Something like the following should work as the filter on Company:

CALCULATE( NOT ISEMPTY( UserAllowedCompany ), User[Id] = USERPRINCIPALNAME() )

Note that, even though you should also have the User[Id] = USERPRINCIPALNAME() filter on User, the RLS filters aren't evaluated in order, so you need to re-incorporate the underlying USERPRINCIPALNAME() filter any time you are defining a new RLS filter.

Here are some screenshots with a dummy model similar to yours in case helpful.

Re: Dynamic RLS with multiple tables

debenaire — Tue, 31 Oct 2023 17:55:36 GMT

MarkLaf thank you so much!! My model has many to many from user to UserAlkowedCompany because the id between them is not the user Id but the Licensee-UserRoleId which can be assigned to many users. Does this make a difference as this is not working in my model. Thanks

Re: Dynamic RLS with multiple tables

MarkLaf — Tue, 31 Oct 2023 20:04:35 GMT

It should still work similar to my solution. Just ensure that the M:M has filtering direction set where User filters UserAllowedCompany.

That said, I would recommend changing query to join the User Id onto UserAllowedCompany and then setting up a 1:M relationship between User and UserAllowedCompany as set up in the dummy model I showed.

If updating the query isn't possible or desireable, I can update my original post to include a M:M relationship between User and UserAllowedCompany. Just let me know

Re: Dynamic RLS with multiple tables

MarkLaf — Tue, 31 Oct 2023 20:31:05 GMT

Actually, I had some time now. Here is original post, tweaked for User : UserAllowedCompany to have M:M relationship.

The filter from User will propogate as long as it travels through a single direction filter. So, either one --> many relationships from the one side OR single filter direction many --> many relationship from the left (A) side of A filters B.

As you've noted, it doesn't work nicely when traveling from the other direction, many --> one, like UserAllowedCompany (many) --> (one) Company.

Something like the following should work as the filter on Company:

CALCULATE( 
  NOT ISEMPTY( UserAllowedCompany ), 
  User[Id] = USERPRINCIPALNAME() 
)

Here are some screenshots with a dummy model similar to yours in case helpful.

Re: Dynamic RLS with multiple tables

debenaire — Wed, 01 Nov 2023 11:20:50 GMT

MarkLaf thank you so much for your help with this.