Performance Optimization for Power BI Row Level Security Model with Bidirectional Relationships

[Anonymous]

I am working on a Power BI project where I need to implement Row Level Security (RLS) to control what data users can see. The model is expected to serve up to 1000 users, thus performance is important. These users are not all expected to be concurrent users, but some proportion could be.  The RLS filter uses the DAX function USERPRINCIPLENAME() to identify the logged in user, and filter data to just records related to their respective company.

Here is my  PBIX file and below is a screenshot of my model: 

In this model, I use "cross filter direction" set to "Both" in two relationships, which as I understand, will negatively impact the model performance. Microsoft's guidance on Bi-directional relationships indicates the same.

The RLS in my current model works fine, on the sample dataset (see screenshot).

I've investigated two approaches suggested by Reza Rad in his RLS book (link to book), to eliminate the need for bidirectional relationships. Specifically:

1. Redesigning the model
2. Using PATH functions (Path() ,& Pathcontains()

Unfortunately, both methods have yet to yield successful results.

Therefore I am looking for suggestions on how to eliminate the Bi-directional relationships.

Here's a brief overview of my data model:

• Table 1: Client Company Users This table contains user details. Users are linked to a company in the “Client Company” table via the “Company Name” field. Multiple users can be linked to a single company.
• Table 2: Client Company Each company in this table is linked to multiple transactions in the “Client Company Sales” table.
• Table 3: Client Manager A Client manager oversees multiple Client Companies. They are linked to Client companies via “company ID”.

I appreciate any insights or suggestions you might have to eliminate Bi-directional relationships from this model.

Thank you in advance!

[Anonymous]

@jdbuchanan71 Great suggestions.  I will test each suggestion. These changes, combined with load testing - as outlined in this Chris Web's post should hopefully cover off our needs. 

[jdbuchanan71 · Accepted solution]

@Anonymous 

I think your use of bi-directional to drive RLS should be ok but I would combine the users together in a single table.  It looks like it is always a UPN is related to 1 or more Company_ID so you should be able to combine those tables 'Client Manager' and 'Client Company Users' into 1 table. 
You would need to add the field [Client Company ID] for the users in the 'Client Company Users' user table but then your master Users table would link on [Client Company ID].  It is faster for the model to traverse a relationship based on shorter field length so joining on [Client Company ID] is a better choice than using [Company Name].
I would also remove the GUIDS [ZAP employee ID] / [Unique User ID] unless you really need them.  If they are not being used for anything then they are just taking up space in the model.

Another option would be to filter the company list from a disconnected user table using an filter on the company table like this.

VAR _UPN =
    USERPRINCIPALNAME ()
RETURN
    'Client Company'[Company ID]
        IN CALCULATETABLE (
            DISTINCT ( 'Users'[Client Company  ID] ),
            'Users'[Email Address] = _UPN
        )

Again, I would do this against a single user table.