Power BI Dynamic RLS Role

SebRosolen — Wed, 08 Mar 2023 10:36:26 GMT

Hello everyone,

I am trying to set up a dynamic RLS role so that users who belong to the role can only see the data based on the email address that they used to log in. I need to map the email addresses to a "tenant_id", which will be the main filter that is used to filter all other tables.

My idea is to set up an additional table called "power_bi_users" with two columns, 'user_email' and 'tenant_id', then use dynamic RLS to specify that power_bi_users.user_email must equal username(). That row filter will then be passed through through to the tenant table via the tenant_id, which passes the tenant_id through to all other tables.

My question is whether or not this is the best approach? Or is there a way to use another function instead of username() which calls a custom "tenant_id" field instead of returning an email address?

Thanks in advance!

Seb

Re: Power BI Dynamic RLS Role

luisrh — Thu, 09 Mar 2023 00:54:47 GMT

We have been using CustomData() since the beginning of time in Analysis services, and over a year ago, MSFT added that capability for RLS in Power BI. This is another way of doing it. The DAX filtering is very flexible, though and you can pretty much target any fields. The most important thing with RLS is to get your modeling correct so all dimension filters work correctly with measures, etc. See below for info on that feature -

The CustomData feature is now generally available in Power BI! | Microsoft Power BI Blog | Microsoft Power BI

topic Re: Power BI Dynamic RLS Role in Developer

Power BI Dynamic RLS Role

Re: Power BI Dynamic RLS Role