Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Help with RLS for inactive relationships with USERELATIONSHIP

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

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

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

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    Thanks for your feedback.

     

    Best Regards,
    Adamk Kong