Forum Discussion

otets's avatar
otets
Icon for Advocate I rankAdvocate I
3 years ago

RLS and CROSSFILTER Error. Table has two join paths to table

Hi,

I try to use the CROSS FILTER function to avoid bi-directional relationships, and it works fine without RLS.

But when I try to use the same measure with RLS then, I receive the following error: "Join paths are expected to form a tree, but the table 'Revenue' has two join paths to table 'Org': 'Revenue'->'Org' and 'Revenue'->'Org'->'Metric'->'Org'"

I can reproduce it on the simple model and data.

Measure =

    CALCULATE(
        SUM(Revenue[Revenue]),
        Metric[TypeID] = 1,
        CROSSFILTER(Metric[ProjectID], Org[ProjectID], Both)
    )

 

RLS (static for now) filters the SBU table.

Metric and Revenue - fact tables.

I have to propagate the filter by metric type from the Metric table to calculate the revenue (that is why I try to use CROSSFILTER and try to avoid both relationship directions)

 

If I remove "Metric[TypeID] = 1" from measure then it is work.

 

Could you please explain this error and help to resolve it?

Need Help

2 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Hi, otets,

     

    so something to do with how RLS is propagated from the "RLS"-table to the fact tables, I don't understand fully why it happens. 

    But you have a couple of opportunities to rewrite it, like this:

     

    Measure =
CALCULATE (
    SUM ( Revenue[Revenue] ),
    TREATAS (
        CALCULATETABLE ( VALUES ( Metric[ProjectID] ), Metric[TypeID] = 10 ),
        Org[ProjectID]
    )
)

     

     

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

     

     

    • otets's avatar
      otets
      Icon for Advocate I rankAdvocate I
      3 years ago

      Hi, sturlaws 

      Thanks for your comment.
      It works fine with RLS but not without it. It is a bit confusing for me.

      The result without RLS

      The measure RevenueByMetricType_1 with TREATAS solution
      The measure RevenueByMetricType I have written recently. Here is the code

      RevenueByMetricType = 
    CALCULATE(
        SUM(Revenue[Revenue]),
        SUMMARIZE(
            CALCULATETABLE(Metric, KEEPFILTERS(Metric[TypeID] = 1)),
            Org[ProjectID]
        )
    )


      When I view it with RLS, the result is


      I still don't understand this mystery with RLS and CROSSFILTER.