Help Setting up Dynamic RLS with Multiple Conditions

[ptmuldoon]

I'm hoping someone can possibly help and explain the proper way to set up RLS filters with 'multiple conditions'.  I've been trying to do this following the below info from Radacad, and not use 'Apply security filter in both directions'.

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

What I'm trying to do is based on the User email/login, to filter the users table. I then have 3 other tables that I would like to then also filter down based on user's permissions.

When logged in, I am trying to 1) filter and control what Regions or 'PropertyGroup', 2) Filter what Departments in those Regions they can see, and 3) What accounting/GL accounts they have the right to look at.

So in my sample, I have User 4. This users should have access to Region 4 and those properties in Region 4. They should then also be able to view only the 'Rooms' department of those particualar properties, and then also should only have access the Revenue accounts of those properties.

I've been struggle this the better part of day now, and finally time to try and learn and hope someone can take a look at help explain the proper steps.

EDIT.  I can't seem to attach a sample pbix file?  

I posted the sample pbix file here:   hXXps://drive.google.com/file/d/1eg-lPVOKGRZoH2-IaCjjkksSVuoXoQLc/view?usp=sharing

and the sample excel tables file if easier as well here: hXXps://docs.google.com/spreadsheets/d/1wYQatHfRi-_m5sNcgewNQDiU-ty4tOgr/edit?usp=sharing&ouid=103613584984100370152&rtpof=true&sd=true

I set the 'Manage Role' for [Email] = USERPRINCIPALNAME(). But then I get confused on how to actual filter the other tables based on the users table.  And below is the basic relationships.

[v-linyulu-msft · Accepted solution]

HI,@ptmuldoon 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Next create relationships between tables:

 3.Then, I created two measure to make it easy for you to see the change:

u1 = USERNAME()

upn1 = USERPRINCIPALNAME()

4.Then I also configured two different rls:

5.In desktop:

6.In service:

7.The above results verify that username() uses the "domain \ username" format in Power BI Desktop. In the Power BI service and the Power BI Report Server, the user's User Principal Name (UPN) format is used. Alternatively, you can use userprincipalname(), which always returns a user in its userprincipalname format username@contoso.com.

8.Verify the rls role below:

9.From the above, if you need to use the rls of the primary table to restrict the access permission of other tables, it is best to establish a one-to-one relationship, or establish a one-to-many relationship, otherwise rls will not take effect.

10.The following documents may help you:

Security in Power BI embedded analytics - Power BI | Microsoft Learn

Row-level security (RLS) with Power BI - Microsoft Fabric | Microsoft Learn

If you need us to know more about you, could you please re-share the link? We are temporarily unable to obtain valid data through the link you share, so that we can not conduct further analysis based on your data structure. Please take care to remove sensitive information.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.