ALLEXCEPT + USERELATIONSHIP

[Kbal]

Hi,

I have the following model :

In the table KM_Final, I have a circular dependancy between 2 calculated columns :

- 

PFS2_Count = 
var records = CALCULATE(COUNT('treatments'[id])
    ;USERELATIONSHIP('treatments'[PFS2_ProtoMonthsLine] ; KM_Final[ProtoMonthsLine]) 
    )
RETURN IF(ISBLANK(records);0;records)

PFS2_Running = 
SUMX(
    FILTER(
        ALL(KM_Final)
        ;[ProtoMonthsLine] <= EARLIER([ProtoMonthsLine])
    )
;[PFS2_Count])

The PFS2_Running should cumulative SUM the PFS2_Count, in the same way as PFS_Running do it to PFS_Count :

The following link, tell me to add an index (I did it in table treatment but no effect), or to use ALLEXCEPT :

https://www.sqlbi.com/articles/understanding-circular-dependencies/

So, in the first column PFS2_Count, I want to add : ALLEXCEPT(KM_Final ; KM_Final[PFS2_Running]) to avoid circular dependency.

But I don't know how to combine ALLEXCEPT and USERELATIONSHIP.

Doing this don't give me the good result in PFS2_Count:

Please someone have an idea?
Thank you,

[v-piga-msft]

Hi @Kbal,

For your scenario, I'm afraid that if you add : ALLEXCEPT(KM_Final ; KM_Final[PFS2_Running]) to the first column PFS2_Count, it will still have the error.

For your case, I will suggest create PFS2_Running as a measure that should avoid the circular dependency.

Best  Regards,

Cherry

[Kbal · Accepted solution]

HI @v-piga-msft

Thank you for your reply, I need a column instead of measure to reuse it in another column.

But i have found my error : I was wrongly using ALLEXCEPT to remove filter context from the specified column.

The solution in my case is to use ALL(KM_Final[PFS2_Running]), and the circular dependency dissapears :