Forum Discussion
RLS and USERELATIONSHIP problem
Hi ju97,
Thank you for bringing this issue to our attention. We understand that the error related to the UseRelationship() and CrossFilter() functions has recently impacted your report, despite it functioning well previously.
As this is a known issue that is currently being addressed, we are unable to keep this thread open indefinitely.For official updates, you can check for more information through the provided link : Known issue - Reports that use functions with RLS don't work - Microsoft Fabric | Microsoft Learn
In the meantime, if you continue to experience difficulties, we kindly encourage you to create a new thread. This will allow us to assist you more effectively.
Thank you for your understanding, and please know that we are here to help!
Best regards,
Atheeq.
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.
"
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"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
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.
