cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ansa_naz Member
Member

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

 

RLS Issue.jpg

These are the relationships:

 

RLS Issue1.jpg

 

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

5 REPLIES 5
natelpeterson Senior Member
Senior Member

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

@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 Member
Member

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

Hi @natelpeterson  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?

natelpeterson Senior Member
Senior Member

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

@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

ansa_naz Member
Member

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

I am trying to get sum of ANSAPBICustomerTransDetailed.Outstanding by ANSAPBISite. The existing active relationship will not give this info to me, as it will total by CustomerID instead. However, ANSAPBICustomerTransDetailed does contain a SiteID for each transaction, not sure I can use that in some other DAX magic way to give me an answer, without actually using the active or inactive relationship?

edhans New Contributor
New Contributor

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

You could duplicate the DIM tables and have single relationships if you must absolutely use different releationships for different measures for fields you have definied to have constraints in RLS.