Power BI RLS with email and list of values (Dynamic?)

[datagrrl]

I am trying to implement Row Level Security in Power BI. I have been mostly using Qlik Sense and Section Access for the last 10 years. I am new to Power BI. 

I have a list that looks like this:

UserEmail	State
me@mycompany.com	MI
me@mycompany.com	FL
me@mycompany.com	AK
user2@mycompany.com	MI
user3@mycompany.com	GA
user4@mycompany.com	IN

I have watched quite a few videos and I think I have an understanding, but after publishing I do not see what I expect to see at all.

My account should only have access to the states AK,FL and MI.

If I use the state from the customer table I get access to all states. If I use the state from the RLS table I see all of the values in that table (5 total). 

This is the join:

I am expecting to only see AK, FL, and MI for my account. I have added our emails in the Power BI Service as well and I still get the following.

(This is all fake data, the emails above are fake, but I used real emails when populating the list). The table in the upper left has both the state from the customer table and the state from the RLS table selected. The states that are showing are all available values from the RLS table. My account hsould only see MI,FL,AK.

I would also only expect to see those states on the map, but when I pick the state from the customer table, I get to see everything.

Any assistance is appreciated. 

[v-ssriganesh · Accepted solution]

Hi @datagrrl,
Thank you for reaching out to the Microsoft fabric community forum.

Based on your description and screenshots, it looks like you're almost there with the Row-Level Security (RLS) setup, but a couple of key details are likely causing the unexpected results.

You have a many-to-many relationship between your Customers table and your RLS table (RLS1), where each user can be assigned to multiple states and you’ve applied RLS using a relationship on State, but you're seeing either:

• All data from the Customers table (when using Customers[State]]), or
• All entries from the RLS table (when using RLS1[State])

This is a common point of confusion when setting up RLS with many-to-many relationships.

To resolve the issue, keep the many-to-many relationship between Customers[State] and RLS1[State], ensuring that "Apply security filter in both directions" is enabled and the cross-filter direction is set to Both. In Modeling > Manage Roles, add a DAX filter on the RLS1 table:

[UserEmail] = USERPRINCIPALNAME()

In your visuals, always use the State field from the Customers table, not from RLS1, as the RLS table is only for filtering. After publishing to Power BI Service, assign users to the role in the dataset's Security settings, and test using the "View as Role" feature.

Since you're new to Power BI and coming from Qlik Sense, I highly recommend going through this official Microsoft Learn module: Implement row-level security in Power BI. It covers both static and dynamic RLS with hands-on exercises that match what you're trying to achieve.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[datagrrl]

Thank you. I do keep getting an error that I can not set the cross filtering to both, so I have picked to have the RLS table filter the customer dimension table and it seems to be working as expected.

Thanks for sending me this training. I watched a number of videos, but this training helped the dynamic RLS make sense for me.