Forum Discussion
ALLEXCEPT + USERELATIONSHIP
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,
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 :
2 Replies
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
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 :
