A circular dependency is detected issue

[KristofferAJ]

I have a formula which provides me with an error: "A circular dependency is detected" - and I can't seem to understand why there is a circular dependency.

My assumption is the [Time duration fruit sold] which is a measure?

Any who can see the issue?

Scoring Column =

VAR NCT = CTL[FRUIT]

VAR filteredTable = FILTER ( CTL, NOT ( ISBLANK ( [Time duration fruit sold] ) ) && CTL[FRUIT] = NCT)

RETURN

DIVIDE (

RANKX (

filteredTable,

CTL[Time duration fruit sold],

,

DESC

) - 1,

COUNTROWS ( filteredTable ) - 1

)

----

Measure:

Time Duration fruit sold =

VAR dispdate =

MIN( CTL[Site RTE Actual] )

VAR lastsoldthiscountry =

CALCULATE(

MAX( CTL[Site Last Actual] ),

ALL( CTL ),

SUMMARIZE( CTL, CTL[FRUIT], CTL[Country Name] )

)

RETURN

IF(

NOT ( ISBLANK( lastsoldthiscountry ) ) && NOT ( ISBLANK( dispdate ) ),

INT( lastsoldthiscountry - dispdate )

)

[v-kkf-msft · Accepted solution]

Hi @KristofferAJ ,

Please try these columns.

Time Duration = 
VAR dispdate = CTL[Site RTE Actual]
VAR lastsoldthiscountry =
    CALCULATE (
        MAX ( CTL[Site Last Actual] ),
        ALLEXCEPT ( CTL, CTL[FRUIT], CTL[Country Name] )
    )
RETURN
    IF (
        NOT ( ISBLANK ( lastsoldthiscountry ) ) && NOT ( ISBLANK ( dispdate ) ),
        INT ( lastsoldthiscountry - dispdate )
    )

Scoring Column = 
VAR NCT = CTL[FRUIT]
VAR filteredTable =
    FILTER ( CTL, NOT ( ISBLANK ( [Time Duration] ) ) && CTL[FRUIT] = NCT )
RETURN
    DIVIDE (
        RANKX ( filteredTable, [Time Duration],, DESC ) - 1,
        COUNTROWS ( filteredTable ) - 1
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

Refer to this article for a detailed explanation of circular dependency in DAX:

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

The basic issue is that you are trying to define a new column that depends upon a measure that includes the expression ALL ( CTL ), which refers to the entire table you're attempting to add a column to, so it's indirectly referencing itself (since the new column would be part of that entire table). This is just a high-level heuristic explanation though. Please read the article I linked above.

The article mentions using ALLEXCEPT as one possible resolution, so you may want to try this modification to the measure:

Time Duration fruit sold =
VAR dispdate =
    MIN ( CTL[Site RTE Actual] )
VAR lastsoldthiscountry =
    CALCULATE (
        MAX ( CTL[Site Last Actual] ),
        ALLEXCEPT ( CTL, CTL[FRUIT], CTL[Country Name] )
    )
RETURN
    IF (
        NOT ( ISBLANK ( lastsoldthiscountry ) ) && NOT ( ISBLANK ( dispdate ) ),
        INT ( lastsoldthiscountry - dispdate )
    )

[KristofferAJ]

Thanks for pointing this out. I can see what you mean, and indeed its complicated. And I might consider trying to retrieve this data in another way.

Im trying get the difference days from first sold type FRUIT to latest sold in a specific country.

So in theory the time between first sold ORANGE to the last sold ORANGE in one specific country.

My data would have several different fruits and countries..

[daXtreme]

Watch this: Avoiding circular dependency errors in DAX - YouTube