Forum Discussion
Users with multiple security roles suddenly can't see data
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?
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.
let Source = List.Distinct(#"Current Dataset"[Client]), #"Sorted Items" = List.Sort(Source,Order.Ascending) in #"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.
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.
let
Source = List.Distinct(#"Current Dataset"[Client]),
#"Sorted Items" = List.Sort(Source,Order.Ascending)
in
#"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.
This solution partially works, but in my testing the intermediary table is not actually being filtered by the RLS evalution tied to the "Current Datasets" equivalent in my model even with cross directional filtering set to "Both". The intermediary table is only filtered by RLS if "Allow security filter in both directions" is enabled, which, as you mentioned, causes the same error. Did you add another RLS formula to the intermediary table?