RLS and USERELATIONSHIP problem

[ju97]

A long standing report has recently had a large majority of visuals show the error when RLS controlled users access:

"The UseRelationship() and CrossFilter() functions may not be used when querying ‘TableA’ because it is constrained by row-level security defined on ‘TableA’ or related tables"

I'm aware of this error and why it occurs, but the report has been working fine, hasn't had any major changes recently, and this issue has only appeared over the weekend.

Can you please confirm if you are aware of that issue and if we can expect some help from you?

[robertnorman]

Also, I understand my English was short of terrible in that last paragraph. That is becase a) English is not my native language, , but i tried myself at this because it was an important matter, i fuelt.

2) Go **bleep** yourself.

I dont want world domination even though Sweden controlled most of Poland, or parts of Germany and most of the Baltic states, back in the 1600's. If you look at an old map, youre poor. Everyone can have a claim for anything then. Best whished to ya, considering bug reports, sorry for goint OT

[AlexNL]

We've got the same issue here. Can somebody please confirm if this issue is a bug or not?

In my opinion it is a clear bug. It was working fine before and very very helpful and needed. All the community responses confirm this. Also when you test this in Power BI Desktop it all works fine(!). This issue only occurs in Powerbi.com. It is just not logical.

EDIT: We do not have the latest Power BI Desktop release. When we install this the problem will probably occur also local (desktop).

[v-aatheeque]

Hi @ju97 

Glad that your query got resolved and If our response addressed by the community member for your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[AlexNL]

I do not believe that this is a solution everybody is waiting for. A temporary workaround at it's best but highly unwanted.

[Madsdreyer]

I got this explanation from Microsoft:

"

as mentioned in the below public documentations this is a limitation with the feature, this do not necessarily mean that the functionality will not work, it just means that it is a scenario which was not tested by our Product Group, and hence there are no guarantees whatsoever for the present or for the future that it should be working now or will continue to work in the future, because it is unsupported.

USERELATIONSHIP cannot be used when row level security is defined for the table in which the measure is included. 

https://learn.microsoft.com/en-us/dax/userelationship-function-dax#remarks 

 CROSSFILTER is not supported for Row-level security. Please refer to the below document:

https://learn.microsoft.com/en-us/dax/crossfilter-function-dax#remarks 

>We suggest you to avoid using UseRelationship() and CrossFilter() in measures or calculations that are affected by RLS. 

Product Group reserves the right to make any breaking changes to it without any pre-notice anywhere, because it is something that no users should ever be using in any circumstances.

Please note that if you are using unsupported feature you are at risk of having their solution broken at any point of time without any pre-notice and without us being able to support you in any way.

"

[mostvp123]

Interesting. I guess the use of inactive relationships only, was a loophole which has now been closed. Still not sure I understand the rationale behind this decision, as having no active relationships between the RLS Dim table and the fact table does not trigger RLS behavior.

To fix the bug, I had to completely rebuild my model - For each role I now have a separate Dim table, which is linked via a one to many active relationship to my fact table. This allows me to have the same measures and calculations, without needing to use USERELATIONSHIP. The result works, although I now have a Dim table which is duplicated 7 times. I suppose it is what it is - hope this can help someone who is having the same issue and built their model like I did.

[Col_Mar]

Thanks for the update. 
I don't know if you can contact them back and ask additional questions but I don't think their response explains the issues in our reports.  We haven't had a response from our contact.
I did some testing and separated RLS from USERELATIONSHIP.  I took the Dim with RLS on it and copied it.  I applied RLS there and left my USERELATIONSHIP on the old table.  The basic model would look like the below with RLS applied to Dim RLS and USERELATIONSHIP between Dim and Fact tables.  This still did not work and has the same error.
Dim RLS > Dim > Fact
Are Microsoft saying it is no longer possible to use USERELATIONSHIP on any table the security propagates to because this is what seems to be happening...and doesn't really make sense?  The data is already filtered by the security on the previous table?  It is also not what their documentation says; "USERELATIONSHIP cannot be used when row level security is defined for the table in which the measure is included"

[mostvp123]

That is correct, I believe you can no longer use DIM tables with RLS defined on them with any USERELATIONSHIP formulas at all, even if relationships are inactive. 

Your model is still not working as you are still technically using USERELATIONSHIP on a table related to the Dim table, which propagates RLS filters. You may need to do what I did, in my previous comment, to avoid the use of USERELATIONSHIP altogether.

Regarding the documentation - I agree that this sentence does not make sense and this is likely due to poor wording:
" USERELATIONSHIP cannot be used when row level security is defined for the table in which the measure is included" 
What Microsoft probably mean is that USERELATIONSHIP cannot be used in any measure referring directly or indirectly (i.e. via potential relationships propagating to) the table where row level security is defined. 
I can see this easily happening if
1) USERELATIONSHIP directly refers to the Dimensional table with RLS
2) USERELATIONSHIP indirectly refers to the Dimensional table with RLS, if there is a Both ways relationship linked to the Dimensional table which is accidentally getting triggered

[Anonymous]

I have the same issue with many reports now. 

Will Microsoft fix this soon?

[renatochagas03]

It's happening here as well. Yesterday was fine and today broken. I have 40+ dashboards plugged in the same semantic model where all my parent metrics use USERELATIONSHIP with RLS (access control table [BOTH DIRECTION] -> dimension tables [SINGLE DIRECTION] -> [SINGLE DIRECTION] fact tables). My entire audience is now complaining they cannot access the dashboards. We are escalating to Microsoft.

[EdouardLC]

Same issue for my customers..

[mostvp123]

Reposting my reply to another comment on this thread for visibility:

To memory - the purpose of this error is to prevent exposing data to users which they should not see, if there is an active relationship between a dimensional table where RLS is applied, and fact tables in the model. The active relationship propagates RLS from the dimensional table to the fact table. If there was a second, inactive relationship, between the dim table and fact table, trying to enable by using USERELATIONSHIP would cause the error.

However, if there was no active relationship between the two tables (for example, two inactive relationships), USERELATIONSHIP would still work fine. I'm guessing because the lack of an active relationship means RLS is no longer propagating any filters to the fact tables. That is how my model is structured. It seems such a setup is no longer working since this bug started, and in my view, there should be no reason for that. It is almost as if any usage of USERELATIONSHIP between a dimensional table with RLS and fact table is now banned. We need this changed reverted as soon as possible, it also severely limits the usefulness of this formula.

[Madsdreyer]

I have been writing with MS support and the reply is:
"USERELATIONSHIP cannot be used when row level security is defined for the table in which the measure is included."

"so please try to avoid those functions"
Also see this https://learn.microsoft.com/en-us/dax/userelationship-function-dax#remarks

So it is not a bug  ... 

I don't know that to do.... 

[mostvp123]

To memory - the purpose of this error is to prevent exposing data to users which they should not see, if there is an active relationship between a dimensional table where RLS is applied, and fact tables in the model. The active relationship propagates RLS from the dimensional table to the fact table. If there was a second, inactive relationship, between the dim table and fact table, trying to enable by using USERELATIONSHIP would cause the error.

However, if there was no active relationship between the two tables (for example, two inactive relationships), USERELATIONSHIP would still work fine. I'm guessing because the lack of an active relationship means RLS is no longer propagating any filters to the fact tables. That is how my model is structured. It seems such a setup is no longer working since this bug started, and in my view, there should be no reason for that. It is almost as if any usage of USERELATIONSHIP between a dimensional table with RLS and fact table is now banned. We need this changed reverted as soon as possible, it also severely limits the usefulness of this formula.

[Col_Mar]

Our model does not have RLS directly defined on the table in which the measure is included (we have a seperate measures table anyway) but its like the security filter is now not working as it was?  
We also cannot replicate the issue in Desktop by impersonating the RLS user so it feels like if this was intended behavoir it would also give the same error?

[stephennin]

I think if something has been working a certain way for YEARS and then suddenly doesn't it's a bug, but yeah, we are in the same boat, not sure what to do.

[Bbooogie]

Like many others. We got the same problem in some of our reports.

[AndreiP90]

I have the same issue as well, but there is the following issue, on the description of the function it specifies that it shouldn't work with RLS. It is so confusing.

You have a function that works for years and now you patch it up? Just leave it as it is and create a new one for the intended purpose.

[Madsdreyer]

Can you please link the Remarks?

[robertnorman]

According to user, 11 pm Swedish CET+1, everything worked fine. Now we have the same problem. Alot of things bugged out. How do we elevate this? Is reporting to MS support even gonna help to prioritize, and where is the best place to report; is it here: https://app.powerbi.com/admin-portal/supportCenter?experience=power-bi

[Col_Mar]

We have the same issue.  Seems to have appeared all of a sudden on a report which wasn't an issue before.