PowerBI relations

[lfrigione]

Hi,
I have three tables:
Facility(FacilityId, FacilityName, ...)
AgeRange(AgeRangeId, minAge, maxAge, FacilityId, ...)
ClientPeriod(ClientId, From, To, AgerangeId, FacilityId, ...)

I have two active relations between Facility-AgeRange and Facility-ClientPeriod that allows me to filter visible contents for the logghed user (one user can see only data of a specific facility).

Then I have created a third relation between Agerange-ClientPeriod.

I have that simple measure 
Test = CALCULATE(COUNTROWS(ClientPeriod), USERELATIONSHIP(ClientPeriod[AgeRangeId], AgeRange[AgeRangeId]))
that I wont to plot in a table that shows me how many period I have for each AgeRange.

When I try to do that I recive this error: 
Join paths are expected to form a tree, but the table 'ClientPeriod' has two join paths to table 'Facility': 'ClientPeriod'->'AgeRange'->'Facility' and 'ClientPeriod'->'Facility'.

Very important: I filter Facility Table with RLS, If I do not use RLS it works.

How can I solve that problem?
Thank you.

[v-shex-msft · Accepted solution]

HI @lfrigione,

It seems like userelationship and RLS conflicts with the real and virtual relationship mappings.
Have you try to removing the virtual relationship and manually using the DAX expression to apply a filter in your calculations?

Test =
VAR inativeFilter =
    VALUES ( AgeRange[AgeRangeId] )
RETURN
    CALCULATE (
        COUNTROWS ( ClientPeriod ),
        FILTER ( ALLSELECTED ( ClientPeriod ), [AgeRangeId] IN inativeFilter )
    )

Regards,

Xiaoxin Sheng

[lfrigione]

Thank you @v-shex-msft 

[amitchandak]

@lfrigione , Facility should not join with AgeRange, it should only join with ClientPeriod,

if need have one inactive join when needed

[lfrigione]

Hi @amitchandak ,

thank you for replying.

I need this relation because evey Facility has it's own age ranges.

When one account log in I would like to use RLS to filter this content.