Forum Discussion

ansa_naz's avatar
ansa_naz
Continued Contributor
7 years ago

Measures not working when RLS is turned on - Join paths are expected to form a tree

Hi all

I have the following error when RLS is turned on (this error does not show when RLS is off):

 

Join paths are expected to form a tree but the table has two join paths

 

These are the relationships:

 

 

I have an inactive relationship. This inactive relationship is used in the measure with the problem. But as it is inactive, I would have thought it wouldnt be an issue?? The measure is:

 

TTipsInvs =
VAR SalesValue =
    CALCULATE (
        SUM ( ANSAPBICustomerTransDetailed[Outstanding] ),
        USERELATIONSHIP ( 'ANSAPBICustomerTransDetailed'[SiteID], ANSAPBISites[Site ID] )
    )
RETURN
    IF ( ISBLANK ( SalesValue ), 0, ( SalesValue ) )

Any way to avoid this issue when RLS is turned on?

Cheers for all help

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    ansa_naz  - The USERELATIONSHIP function activates the relationship. The documentation discusses a problem that looks like it applies to your situation:

     

    "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."

     

    Also, it looks like you have a single Fact Table. It may be beneficial to revamp your model to a Star Schema, if possible.

     

    Cheers,

    Nathan

    • ansa_naz's avatar
      ansa_naz
      Continued Contributor
      7 years ago

      Hi Anonymous  I already have multiple fact tables. I just need to figure out a way to get the measure working without an inactive relationship being used within it. Any ideas?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        ansa_naz 

        Will the measure work without changing the relationship used?

         

        Otherwise, I think you need to change your model. The multiple paths are harming your functionality.

         

        Specifically, unless there are very complex requirements, look into:

        1. Separate entities into dimension tables and model as star schemas around a fact tables

        2. Avoid bi-directional relationships  

         

        Hope this helps,

        Nathan