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.
Community ChampionAgain haven't got a totally clear explanation in my head but is it possible that a recent data refresh introduced blank values on one or both sides of the relationships causing issues?
Not new ones. There are blanks in one of the year columns used as a key between the Actuarial table and Current Dataset, but that has been the case for a while and is designed to keep those rows from showing up in certain calculations. I guess I could try setting them to some other value and excluding them manually.
Thanks for your continued help. I get the feeling we're probably going to have to go with changing the security roles to work around this, but I appreciate the effort you've put into this.
- bcdobbs
Hi again spowell,
Did you say that you can't recreate the issue using view as role (clicking more than one) in power bi desktop? If so what version are you using? I'm wondering if there has been a feature change in the latest version/service.
In the demo I've just built the many to many relationship now displays as limited (which it always would have been):
I've just not seen it flagged in the diagram before apart from when using composite models which prompted me to ask question.
I can also recreate the error in power bi desktop with the above:
Replacing the many to many relationship with the following (bidirectional security off) and moving security filter to State table lets everything work. (The state table can be hidden and not used anywhere). - bcdobbs
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?
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!
- bcdobbs
I'll do a bit of testing later this morning to see if I can recreate a transient issue with many-many.
Until then
1) Is it possible to tweak data model to avoid many to many by building an intermediate dimension table (use DISTINCT (table[keyname]). If you use that in visuals you can strip out the bidirectional aspect.
2) Is the security filter running on that table for security reasons or to tidy things up? If the later there might be other options. For example hiding options in slicers can be done with a measure:
INT ( NOT ( ISEMPTY ( FactTable) ) ) set to "Is 1" on the visual level filter of things like slicers.
Security can then just be applied to a couple of key dimension that need it rather than all.
3) Obviously if less work you're right that building security to be 1 role is a good option.
- AlexisOlson
Super User
Nope. I have seen and can't find any relevant documentation.
- bcdobbs
Anonymous can you give any information from Microsoft on this?
BrunoMAdvocate I
Hi bcdobbs,
We have recently discovered (it appeared in December) exactly the same problem in our organisation where we use premium capacities to publish our PBI Apps.
On Power BI Desktop, the issue is reproducible only with the December version Version: 2.100.785.0 64-bit (décembre 2021)
For my point of view it is actually a regression of the datamodel behavior in power BI and Microsoft team should communicate on this pb because a lot of our reports are impacted and we the workaround is not very easy to implement.Let me describe how to reproduce it with a simple data model :
Table A *=>* Table B with the Shipment numberHere is the M code :
// Table A
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddI5jgJBDAXQu3RM4H0JBzjA5C3uf40pcCG1R1UhevrVxt/nefwct4MRMBGVYvz4BcDjdbsIM6GvxMNBlhm1JGmZewm9UzkzfBWUIEhYSZoE20pUNF1XEqhOWSL/JkjgmdGPPL7fCQybE3gTH3+Hp0QTAxaSkuyvMTDSRxCaxJhZ6jXE/h0RjnptRN/y/MoYCWaGm2SKUe0apUmkqk/RJkYhUhtF65mAsYUS76KEwiXRL+RyO7S9HdreDm1vh7ad2rZT33U6N7rodG500enc6KpT3XZq207HbK8/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BU = _t, #"Shipment number" = _t, #"Picking location" = _t])
in
Source// Table B
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdBBDsMwCATAv+ScAyxg4C1R/v+NulIveCv5tMJrPM9zmYq2aqCu+9LrvX+RGTRHlJXicypWw8cUvnM9p7wgLbN+edmaXR6dMaLSSPRR32Jzqktrzfrca9mMlpjDjz+KKeaLewU/utyt5sUU2Wd2tS/k0RWRM1oo9/ntKtn7zyigbv/twfZge7A92B5sD7YH24PtwfZge7A92B5sD7YH24PtwfZge7D9jt4P", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Shipment number" = _t, Item = _t])
in
Source3 RLS are created :
RLS A: [BU] = "A"
RLS B: [BU] = "B"
RLS A or B: [BU] = "A" || [BU] = "B"When i simulate the display with "RLS A" and "RLS B" ticked, the visual is in error.
When i simulate the display with "RLS A or B", it is OK and the visual is displayed correctly.
I do not have the tool to capture the sql statement used in the 2 differents usecases, but i think it would be very helpful to prove there is a regression.
Bruno- bcdobbs
Microsoft are aware of the issue. See the post from Anonymous in Dashboard with multiple RLS roles work in My Workspace but fails in Premium workspace
