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?

[jvlsking]

Guys, I’ve got the solution for this! I built a data lake using Microsoft Fabric at the company where I work, and I ran into the exact same issue you’re facing (even though this problem didn’t happen before). Here’s the solution:

I imagine some of you are assuming that you can’t use USERELATIONSHIP with RLS, but that’s not true. You actually can. So then, why is this error happening? Well, you need to take a look at the relationships between your tables. Let me show you my example:

Whenever there is an inactive relationship in a table that receives RLS, you CANNOT have a filter going in that direction. In the example below, in the image on the left, the table tb_vagas_gupy is filtering tb_calendario, which in turn is filtering tb_colaboradores, with one active relationship and another inactive one. What happens here is that the filter is trying to go through the inactive relationship as well, and RLS does not allow this, which is why you get the error.

In the image on the right, the relationship goes from tb_calendario filtering tb_vagas_gupy. Now it works, because tb_vagas_gupy is no longer indirectly filtering tb_colaboradores. But you may be wondering: “Wait, isn’t tb_calendario also filtering tb_colaboradores? Shouldn’t the same error happen?” The answer is NO. That’s because tb_calendario is derived from tb_colaboradores, so every record that exists in tb_calendario also exists in tb_colaboradores. Therefore, no actual filtering is being done since both tables contain the same data. That’s not the case for tb_vagas_gupy. This last table does not contain all the records from tb_colaboradores, which is why it was applying an active filter.

If you’d like more details, here’s a video in English from an Italian guy who explains this really well:
https://www.youtube.com/watch?v=sC9XT8tdAs4&ab_channel=SQLBI

[v-aatheeque · Accepted solution]

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.

[robertnorman]

We had to do a workaround for this to work. Unfortunately, I didnt post back. We got a 30 days trial to do some workaround, and fortunately we could use an intermediate table, so we have already solved it. If someone would need some tip or help, please let me know, maybe our solution could work for someone else too.

Cya all, take a deep breath.

[MrMani]

We had the same issue in the last few weeks. The following workaround fixed this issue until MSFT fix this issue permanently.
Try
INTERSECT(VALUES('Table 1'[Col1]), VALUES('Table 2'[col2]))
OR
TREAT AS (VALUES('Table 1'[Col1]), 'Table 2'[col2])
instead of 
USERELATIONSHIP('Table 1'[Col1], 'Table 2'[col2])

FYR, Error mesage realted this issue

[CBP]

This worked for me, thank you. Curious about the performance implications on INTERSECT vs USERELATIONSHIP though.

[MrMani]

It depends upon the model design/size, Pls validate the visuals with Performance Analyser in PBI desktop. Pls be informed it's a work around for recent issues using USERELATIONSHIP.

[AlexNL]

Thank you for sharing. Unfortunately in our situation your workaround does not work 😞

[AlexNL]

Like what everybody is replying: The problem still exists. But is there any development in this issue at the moment? Can we expect a fix soon?  

Do somebody know this? Please share.

[v-aatheeque]

Hi @AlexNL ,

Since this is an knonwn issue from Microsoft end , as of now the team has suggested to follow the below approach :

" To fix the issue, customer can remove CROSSFILTER and USERELATIONSHIP from their measures. Alternatively, customer can modify the relationships as per public documentation recommendations for RLS models.
Active vs inactive relationship guidance - Power BI | Microsoft Learn
Dynamic row-level security with Analysis services tabular model - Power BI | Microsoft Learn
USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

If the customer is reporting the issue accessing PowerBi reports with error:
"The USERELATIONSHIP() and CROSSFILTER() functions may not be used when querying 'T' because it is constrained by row-level security".

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[AlexNL]

Hi @v-aatheeque 

Is there any news?

[v-aatheeque]

Hi @AlexNL ,

Thank you for following up. we wanted to provide an update that the issue is still under investigation and has not yet fixed.

We sincerely appreciate your patience and understanding. we will keep you updated with any progress.

[wdv72]

Is there any update on the investigation and when we can expect a fix?

[v-aatheeque]

Hi @wdv72 ,
Apologies for the issue.
Still the issue is in active state , please refer the below link for reference :Known issue - Reports that use functions with RLS don't work - Microsoft Fabric | Microsoft Learn

[AlexNL]

The solution is not accepted because it is not a solution.

Please beware this is a very serious issue. We are talking about security and functionality that was working for years. You cannot just say that the functionality is changed and therefore all models have to change. This is fundamental. So please.... fix this bug(!)

[v-aatheeque]

Hi @AlexNL ,

Thank you for bringing this to our attention. We understand the importance of this functionality and the impact it has on your workflows.

We want to assure you that this is a known issue is currently under investigation. support team is actively working on it, and it was considered as a high priority. We sincerely appreciate your patience and understanding.

[wdv72]

Hi @v-aatheeque ,

can you give us an estimate in which time we can expect a solution? The solution should be directly available as it was working the previous versions. Why not apply a rollback untill the latest version is fixed? 

[AlexNL]

Thank you very much for this update. 🙂

[MarkDenHeijer]

Hi, 

This is again a non-solution. Removing the UseRelationship function from your measure without replacing it with something else will return other results. Can you verify if TREATAS is accepted as a valid replacement for these cases? Or will this cause similar issues? 

[Bbooogie]

I dont know if TREATAS is an valid replacement from Microsoft, but in my models have switched to TREATAS and it seems to work as UseRelationship used to work before. (No error messages or changed behaviors). 

[AlexNL]

@MarkDenHeijer Can you please send the syntax for this replacement? I am not sure if I understand it correctly. THANK YOU