Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Users with multiple security roles suddenly can't see data



Our organization recently ran into a problem that seems to be coming out of the blue. We have a Power BI report connected to a SQL server that uses row-level security and security roles to manage which of our internal users can see which data. Each of the report's tables has bidirectional security filtering [EDIT: none of the tables have bidirectional security filtering], and as far as I know this has been the case since the beginning. Users are assigned to roles based on which accounts they work on, so all users have multiple security roles and that has also been the case since the beginning. Every security role filters every table in the report. This has worked up until today.


Now many of our users are getting an error that says, "The user belongs to multiple roles that have security filters, which isn't supported when one of the roles has filters affecting the table 'Table Name' with SecurityFilteringBehavior=Both relationships." Some users have different values in the 'Table Name' slot, which suggests this isn't just one table that got its security filter setting flipped for some reason.


Two users with admin access to the workspace where the report is published are having no issues and don't see any errors.


I am at a loss to figure out what is going on, since the report was last republished weeks ago and no settings have been deliberately changed in the meantime. But it may be some setting I'm overlooking. Can you think of any troubleshooting steps I can take? Are there workarounds for this? Every security role filters every table in the report (two dozen security roles at this point x 5 tables), and this worked previously, so not looking for solutions that will require the tables to self-filter by user or that require the report to be restructured (since that would require a lengthy IT approval process) except as an absolute last resort... I just want to figure out what changed so I can undo it.


Thanks for any help you can offer.


ETA additional information: I discovered a few more details talking to users seeing the errors. There are some visuals that are still working and some that do not. Which visuals have an error is consistent between users with different security roles, so my next step will be checking the formulas for each of the columns and measures that those visuals have in common to see if there is any additional filtering behavior in them that might be an issue. I'll check that and post an update here afterward.


It does appear to be a feature change.


I've built exactly the same model in Power Bi Desktop 2.99.862.0 64-bit (November 2021)



Relationship isn't flagging as being limited in this version.


If I view as two roles it works fine:


@AlexisOlson have you seen any documentation on this?

Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

Frequent Visitor

I wanted to return and describe how I solved this issue in a way that allowed us to continue using multiple security roles for users, rather than the cumbersome workaround of having to define a custom role for each user.


For each table that had a many-to-many relationship with the main dataset, I used a step in Power Query to make a list of the unique values of their shared columns (in our case, taking unique values from the main dataset only, because unmatched values in the lookup tables don't matter). For example, the "Client Labels" table was connected to the "Current Dataset" table by client names in a many-to-many relationship where "Current Dataset" filtered "Client Labels" but not the other way around, so I created a list of unique client names, sorted it for human readability, and added it to the data model as an intermediary table.


    Source = List.Distinct(#"Current Dataset"[Client]),
    #"Sorted Items" = List.Sort(Source,Order.Ascending)
    #"Sorted Items"


Then I connected the new intermediary table to the "Current Datasets" table in a many-to-one relationship which allowed two-way filtering, and to the "Client Labels" table in a many-to-one relationship with one-way filtering. (You can't have two-way filtering of the same table from two different relationships, so you will need to prioritize which of the tables controls filtering of the intermediary table and which is subject to filtering only.)


I did this for each of the many-to-many relationships in the data model, deactivated the old relationships, and activated the new ones. This fixed the problem completely.


One thing to keep in mind is that the "Allow security filter in both directions" box needs to be unchecked for the relationships with two-way filtering. If it gets checked you'll have the same RLS error pop up for users with multiple roles.

View solution in original post


@v-yiruan-msft can you give any information from Microsoft on this?

Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Hi bcdobbs,


Yes, I was using November's version of Power BI. I updated to the December version and now I can also recreate the errors on desktop by viewing as multiple roles.


So that at least explains why it's happening now.


Thanks for all your help tracking down the source of the issue. If an intermediary table to break up the many-to-many relationships is viable for us, that does seem like the better long-term solution. Either way I have an explanation of what happened and a couple leads to follow for fixing it. Thank you!

Nope. I have seen and can't find any relevant documentation.



That's what everything I found said, too, and I'm at a loss to explain what changed.


No, this is affecting a number of visuals on the main page of our report, and the user who discovered it is a frequent user who only noticed the issue today.


I am thinking as a temporary solution we're going to rework the way security roles work so that each person has 1 role that uses a DAX expression to filter to the accounts they're assigned to, instead of assigning users to roles at the service level. It will be less convenient but hopefully that will fix it.


Thank you for your help. I'll mark your last post as a solution since it doesn't sound like this is something we should ever have been able to do. (Still scratching my head that we were able to get away with it so long!)

Super User
Super User

Just for clarity is the dataset setup in import mode and the RLS is defined in the dataset? Eg not direct query with SQL server handling the RLS?

Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Winner of T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Find out who won the T-Shirt Design Challenge and the top 3 finalists.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.