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

[otets]

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?

[sturlaws]

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]

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.