Help with RLS for inactive relationships with USERELATIONSHIP

[Anonymous]

Hi!
We would like to use RLS on seller in this testexample with USERPRINCIPALNAME().
The dimension has multiple relationships to the fact table workorders. 

And from the documentation we will get some challenges with RLS and USERELATIONSHIP.

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

USERELATIONSHIP cannot be used when row level security is defined for the table in which the measure is included. For example, CALCULATE(SUM([SalesAmount]), USERELATIONSHIP(FactInternetSales[CustomerKey], DimCustomer[CustomerKey])) will return an error if row level security is defined for DimCustomer.

What options do we have?
- I think it will work if I unpivot the columns so I only get one active relationships, but in that case I will dupplicate a lot of data.

How do you solve cases like this with RLS and USERELATIONSHIP?
Do you have a better solutions to solve this without unpivot?

Example files
https://drive.google.com/drive/folders/1f-0nr36SaxNqTxP1Qyq585p3fhcdt4bx?usp=drive_link

[Villezekeviking]

I wrote a solution here where you modify the model and no longer need multiple relationships.  

https://www.villezekeviking.com/alternative-to-userelationship/

[Anonymous]

Hi @Anonymous ,

Thanks for your feedback.

Best Regards,
Adamk Kong

[Anonymous · Accepted solution]

I think I have found the solution in this articel from SQLBI
"DAX limitations with inactive relationships and row-level security (RLS)"
https://www.sqlbi.com/articles/dax-limitations-with-inactive-relationships-and-row-level-security-rl...

[Anonymous]

Hi @Anonymous ,

You can  modify your formula like below:

# of rows with inactive repationship = 
CALCULATE
(
  COUNTROWS(Orders)
  , USERELATIONSHIP(Seller[Id],Orders[Seller_col2])
)

But note: USERELATIONSHIP cannot be used when defining row-level security for tables containing measure.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

The challenge is that we need to use row-level security for this.