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