Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ansa_naz
Continued Contributor
Continued Contributor

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
Anonymous
Not applicable

@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
Continued Contributor
Continued Contributor

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
Not applicable

@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
Continued Contributor
Continued Contributor

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.