RLS for multiple tables/filters

[Anonymous]

Hello:

We have a multi-tenant database that we're implementing RLS for.  When a user logs in, they belong to only 1 'tenant' and we want to filter out the data, as well as the available values in the filters so they only see their data. 

I've set it up in the image below (typical star-schema), and I set up a role on the 'Users' tables where the email_address = usernameprincipal(), and it is filtering the data correctly.

However, the issue I'm running into is when other dimensions are in the filters pane it's showing all the values.  For example, when the "Hotel" table is in the filters pane, it is listing all the avialable hotels, not just the ones that belong to that user  (when I use Hotel in a visual like a matrix, it shows correctly)  

Do I need to add another table and then role to the 'Hotel' table in order for it to display only the correct hotels?  Right now, I only added a role against the 'Tenant' table.  It's working correctly and as I'd expect for Tenant, just not for Hotel.  There will be other tables (in addition to Hotel) that I'd need to behave similarly only display the values for that tenant.

Any help or ideas is greatly appreciated!

[petrutjim]

any fix on this one? thanks!

[TomB]

Hi,

I am having a similar issue regarding multiple tenancy. Pretty much all possible solutions run into the same "Function X is not allowed as part of the row level security expression on DirectQuery models" error.

Has this problem been solved? These functions work when used in measures, but in RLS they return errors.

Thanks in advance.

[v-cherch-msft]

Hi @Anonymous 

It seems you may filter data through DAX.For further,please check if below articles could help.

https://radacad.com/dynamic-row-level-security-with-profiles-and-users-in-power-bi

https://www.tringuyenminh92.com/index.php/2017/08/11/dynamic-rls-row-level-security-with-power-bi/

For 3rd party Links:

Please understand that this link is provided “AS IS” with no warranties or guarantees of content changes, and confers no rights.

Regards,

[Anonymous]

Thanks for sending over those links.  

One thing I forgot to mention, is that I'm using Direct Query.  Everything I've tried previously, and with the 2 articles you sent over, appear to be using functionality that doesn't apply to Direct Query.  For example, I get errors like "Function 'containsrow' is not allowed as part of the row level security expression on DirectQuery models" when I try methods described in those articles.

Any other thoughts?

[v-cherch-msft]

Hi @Anonymous 

Please check if below post could help.

https://community.powerbi.com/t5/Desktop/Direct-Query-main-advantages-and-disadvantages/td-p/512259

Regards,