CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Aabo
Aabo

Reputation: 213

POWERBI: A circular dependency is detected issue

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 )
    )

Upvotes: 0

Views: 6500

Answers (1)

RADO
RADO

Reputation: 8148

If "Scoring column" is a calculated column in the table CTL, then you do have a circular reference:

  • In the calculated column, you refer to the measure "Time Duration fruit sold"
  • In the measure "Time Duration fruit sold", you refer to the entire table CTL by using ALL(CTL). ALL function returns all columns, including the calculated columns like "Scoring Column". Since to calculate the measure, you need the column, and to calculate the column, you need the measure, you get the circular reference.

To solve this, make Scoring Column a measure instead of a calculated column.

More generally, avoid calculated columns at all - all columns in your data model should be pre-calculated either at the source (in a database), or in PowerQuery. Building them with DAX is a bad practice that should be avoided.

Upvotes: 2

Related Questions