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

[ansa_naz]

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

[Anonymous]

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

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]

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

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]

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.