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?

[AlexNL]

Thank you for your reply. Performance is very important in our organization. Therefore the TREATAS function is not a solution for us unfortunately. Also the second solution not. It is perhaps too difficult for me to explain in short.  Our situation: There is one group of people who can see everything and a second group who can see everything EXCEPT one table which they cannot access. The optional relationship between that special table and the fact table is essential because otherwise the second group will not see anything at all because Power BI always actives all relationships. Even if you do not select an item (column) from a table. So it's like 'all' or 'nothing'.

The USERELATIONSHIP was perfect for this.

[mostvp123]

Performance is indeed important but if your model is small (relatively) or your measures are very simple, you may not experience any degradation in performance. I'd advise you to try the TREATAS solution first on a few measures as its quite simple to implement, and see the impact on performance.

If the impact is large, I'm happy to help but I don't understand your situation. Can you provide screenshots of your model / more details on your setup and RLS needs? "The optional relationship between that special table and the fact table is essential because otherwise the second group will not see anything at all" - did you mean to say the first group in this sentence, rather than the second group? I'm guessing you are activating the relationship to remove data for your second group?

[AlexNL]

Thank you. Yes.... you are right. I meant the first group. (sorry)

Here a explanation in short in the most compact way I can.

If you know a solution it will be very much appreciated.

But like everybody is saying: It was working perfectly fine before. Therefore I'm still hoping Microsoft Power BI developement team will be able to fix the issue asap.

THANK YOU

[robertnorman]

You need to create a new "Dimension" as a security dimension as a workaround. If you understand.

Unfortunately, MS thinks this was a bug before; and now its solved. I'm as frustrated as you but I understand the security breach before, in terms of RLS and OLS, I think. Whatever, you have to solve it with an intermediate table if that makes any sense to you.

[MarkDenHeijer]

Hi, 

I am still not sure I fully understand your case, but I think the measure should look something like this. It generates a numeric value. You can either apply a filter on a visual saying value>0 or not. Maybe this helps you. 

MeasureToUseAsFilter =

/*Apply this measure to the desired visual set to be greater than 0

Value Blank in the security dim is the situation where securitydim is not used.

*/

VAR SelectedSecuritydim = SELECTCOLUMNS(Security_dim, "id", Security_dim[dimId])

RETURN

IF(

    COUNTROWS(SelectedSecuritydim)=0, --This is the case when selecting blank in the Security_Dim

    1,

    IF(

        CALCULATE(

            COUNTROWS('Fact'),

            'Fact'[dimId] IN SelectedSecuritydim

        ) > 0, 1, 0

    )

)

[AlexNL]

hi there,

I did some testing and I believe it is working. THANK YOU

[Poojara_D12]

Hi @ju97 

The error with USERELATIONSHIP() and CROSSFILTER() in reports constrained by RLS typically arises when DAX logic conflicts with RLS rules. If the report was working fine and suddenly broke, it’s likely due to recent changes. These could include modifications to the underlying dataset, relationships, or RLS rules, as well as anomalies in source data. A recent Power BI Service update might also have altered how RLS or these functions are handled. Additionally, changes to the gateway or workspace configurations could contribute to the issue. Review the dataset, RLS rules, and data for any updates or conflicts. If no changes are identified, check Power BI release notes or contact Microsoft support for further investigation.

 

[mostvp123]

As reported by literally everyone else in this thread, no changes were made to underlying reports, the error appeared overnight on stable reports which had not had changes for YEARS. I was able to find a workaround as described in my other comments but it still stands that something was definitely changed from Microsoft's side, which was not described in the release notes and broke extremely critical reports. Denying this is neither honest nor helpful.

[AlexNL]

Thank you for reply but I can say by 100% certaintcy that we did not change anything in the model, data or relationships etc. Everything is exactly the same.

[Bbooogie]

This most be the most non-answer "answer" i've ever seen. Did you actually not read the whole thread? We know what the problem is, and what we can do about it. But on my side i don't like it when a function work fine until breaking changes that WAS NOT IN THE RELEASE NOTES breaks my customers reports.

[GeorgeVepkhvadz]

Same issue here: 

Was working fine for years, and suddenly this. When testing RLS, for Role it shows fine, but for user with this Role I get the error message.  When should we expect it to get resolved? Or should I think of making changes to my model? 

[MarkDenHeijer]

We experience the same issue as many of you do. We have started redesigning the measures. I use an alternative solution. I see some of you are stuck on how to continue, maybe you can use this example on how to accomplish the same result without using (inactive) relationships. 

I use this solution with [selectcolumns in a var, and then filter where value IN var] quite a lot and it performs well and gives you the flexiblity to also get data from tables that aren't even related via relations. I hope this helps those of you who are stuck on how to fix it.

//OLD WAY
//CALCULATE(COUNTROWS(dim_FinancialProject), USERELATIONSHIP(dim_Company[Company_NK], dim_FinancialProject[Company_NK]))

//New Way
VAR SelectedCompanies = SELECTCOLUMNS(dim_Company, "CompNK", [Company_NK])
RETURN
IF(

COUNTROWS(SelectedCompanies)=0
, 1
, IF(

CALCULATE(

COUNTROWS(dim_FinancialProject)
, dim_FinancialProject[Company_NK] IN SelectedCompanies
) > 0

, 1
, 0

)

)

[AlexNL]

Thank you for sharing this bypass. Unfortunately it does not work for us. I think because our situation is not the same. I will try to explain. In short: There is one group of people who can see everything and a second group who can see everything EXCEPT one table which they cannot access.

The optional relationship between that special table and the fact table is essential because otherwise the second group will not see anything at all because Power BI always actives all relationships. Even if you do not select an item (column) from a table. 

There was a solution for this but unfortunately since a couple of days it is broke. 

[v-aatheeque]

Hi @AlexNL ,

Please consider reaching out to Microsoft Support. You can provide them with all the troubleshooting steps you've already taken, which will help them understand the issue better and provide a resolution. They might be able to identify something specific about your admin account setup or provide a solution that isn't immediately obvious.

Below is the link to create Microsoft Support ticket:
How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

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.

[robertnorman]

Hello Robert,

Greetings for the day!

Could you please check the issue behavior now. We received an update form product team that issue fix for the capacity has been deployed. Can you validate the issue now and update us.

We look forward for your response.

Best Regards,
Nidhishree D S [....]


So I reported this in three ways: here on the forum; and on the link I was given here, and on the correct "CHANNEL". It was duboius on whether this was a big or not (or a feature one might add). After some discussion I got the resonse above. Hope it will help everyone; and also (since I was quite upset, but made a not happe me workaround solution, i am not in the best position to review the fix - however MS says everything is working now, so give the, the big thumbs up (for a quick fix - to their standards) - or an aqtual fix. Lets here it if its true.

Bug reports starts here:

[wdv72]

Unfortunately the problem still exists.

[AlexNL]

Thank you for the update. But unfortunately it did not work. The problem still exists. 😞

[MarkDenHeijer]

If MS has rolled out a bug fix last weekend, it didn't fix the issue. It still persists on our side. 

[AlexNL]

Same here. Issue is not fixed.

[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).