Multi-Tenant Relationships with Dynamic RLS

[bbsoftware]

Hi All, 

We are fairly new to Power BI so please bear with us as after hours of searching we cannot find a solution to our requirement. 

We want to build reports that we can push out to our customers based on a single dataset restricted by RLS. We are exporting the data from our ERP application and importing it into Power BI. Every table we import contains the CompanyID (or you can think of this as a tenantid). 

We have a users table that contains the users and the CompanyID they can access - in some cases, some users can access multiple companies so we add a row for each user/companyID combination. We link this to the companies table which contains one row per company mapping the ID to the company name.

Relationship is set up as bi-directions with Apply Security Filter in Both Directions. 

We then apply D-RLS to the Users table based on UserPrincipalName() ie [Username] = USERPRINCIPALNAME(). 

So far, so good. 

We then have four other tables - one containing a list of sales invoices, one containing the customer masterfiles, and two containing the sales rep masterfiles (one to map to the account manager on the customer, and one to map to the sales rep that processed each sale). All tables contain a column for the CompanyID. In order for a user to select an Account Manager in a slicer to filter the list of customers they can see and the list of sales made to those customers, we need to link as follows. 

All relationships show as Many-to-Many due to the same sales rep code or customer code appearing in the dataset for different companies/tenants. This makes sense - not ideal but makes sense. 

The problem we now face is how to apply the User/Company Access security to the report. In order to ensure a user only sees a list of the Account Managers, Customers and Invoices that apply to the Companies they have access to, we have tried:

1. Linking the Companies table by CompanyID to all the other tables.
   This then will not allow us to link Account Managers to Customers or Customers to Invoice Headers
   
2. Linking the Companies table to just the dimension tables (eg the Account Manager table and the Sales Reps table). This doesn't work as it only lets us join Companies to Sales Reps, when we try to join Companies to Account Managers it complains.
   
   
   
3. Do not link the Companies table to any of the data tables, and instead apply RLS on every data table based on the companies the user has access to - eg:
   [CompanyID] in SELECTCOLUMNS(FILTER(Users,Users[Username]=USERPRINCIPALNAME()),"CompanyID",[CompanyID])
   
   This works in that now the user (when we use the View as option) can only see data for the companies they have access to (in aggregate,) and slicers can be used to filter account managers down to customers down to invoices. This is a good start as far as security is concerned to ensure no user can access any data for companies that aren't associated with their login. This however is not a method we have seen used on any of the examples and guides online so we are wondering if there is a gotcha somewhere...

#3 looks like the winner so far, however this presents the next problem. Lets say a user logs in with access to two companies but they want to filter down to just view a single company's data today.

1. We can add a slicer in based on the Companies table but as in #3 above the companies table doesn't link to any other table, this is pointless.
2. We could add a slicer to the CompanyID field on the Account Manager table to select which company the user wants to view, but as sales rep code 01 exists in both of the companies the user can access, the user will still see any customers in the dataset associated with a rep code available in the selected company. (eg below, selecting company DO from the account managers table slicer is still counting customers in the other company (TO) that have an account manager code present in the selected company)
   
3. We have tried to tie the RLS on all the data tables to a Measure for which company the user has selected (or all available if none is selected), and enabling a slicer on the Companies table. 
   In the Role for each data table:
   [CompanyID]=[SelectedCompany] || (ISBLANK([SelectedCompany]) && [CompanyID] in SELECTCOLUMNS(FILTER(Users,Users[Username]=USERPRINCIPALNAME()),"CompanyID",[CompanyID]))
   
   Where the measure is defied as:
   SelectedCompany = SELECTEDVALUE('Companies'[CompanyID])
   
   I'm guessing measures don't work when setting up roles as SelectedCompany must be blank when applying the RLS given that all data is still displayed even when a single company is selected. The measure is working because SelectedCompany displays correctly in a card visual. 
   
4. We have tried #3 in RLS without using a measure. eg:
   [CompanyID]=SELECTEDVALUE('Companies'[CompanyID]) || (ISBLANK(SELECTEDVALUE('Companies'[CompanyID])) && [CompanyID] in SELECTCOLUMNS(FILTER(Users,Users[Username]=USERPRINCIPALNAME()),"CompanyID",[CompanyID]))
   The result is the same as #3. 

So we are now at a loss of what else to try. 

Even if we split each of our clients into their own workspace with their own data set, some clients have multiple companies that they may want to see in aggregate or seperately so we will still have the same problem. 

Can anyone provide any guidance on how to proceed or if we have got this wildly wrong from the start?

[bbsoftware]

Hi Jay, 

To simplify the problem, see below. 

All tables need to filter by the company the user selects in our 'Company Selection' slicer so that the slicers in the report for Customer Group, Sales Rep and Market Code only show entries valid for that selected company. I then need to be able to filter my customers by Customer Group, Sales Rep or Market Code using slicers. I cannot enable the inactive relationships because: 

If I remove the link between Companies and Customers, then I can only enable one of the links between Customers and Customer Groups, Sales Reps or Market Codes before I get a similar message. 

In this case, RLS is not an option as the user has access to multiple companies but may want to drill down into a data for a single company. 

Hope this makes more sense.

Chris

[Anonymous]

Hi @bbsoftware ,

Have trouble to follow. Would you please share some sample so that we could do some test?