Strange! Unclear circulrar reference

[AhmadBakr]

I have 2 calculated columns:

Weeks Bin =  // calc col to order dispatch weeks in bins to prepare for a histogram

    SWITCH(

        TRUE(),

        [dispatch weeks] <= 2 , "≤ 2"  ,

        [dispatch weeks] <= 4 , "2-4"  ,

        [dispatch weeks] <= 6 , "4-6"  ,

        [dispatch weeks] <= 8 , "6-8"  ,

        [dispatch weeks] <= 10, "8-10" ,

        [dispatch weeks] <= 13, "10-13",

        [dispatch weeks] <= 26, "13-26",

        [dispatch weeks] <= 39, "26-39",

        [dispatch weeks] <= 52, "39-52",

        "> 52"

    )

Index =  // calc col to introduce a parameter for sorting the bins as they are not numerical

SWITCH(

    Stocks[Weeks Bin],

    "≤ 2"  ,  1,

    "2-4"  ,  2,

    "4-6"  ,  3,

    "6-8"  ,  4,

    "8-10" ,  5,

    "10-13",  6,

    "13-26",  7,

    "26-48",  8,

    "48-52",  9,

    "> 52" , 10

)

Power BI desktop throws an error for the [Index] definition! : "A circular dependency was detected: Stocks[Weeks Bin], Stocks[Index], Stocks[Weeks Bin]."

I do not see any circular reference! If I delete the [Index] column, [Weeks Bin] works fine, and [Index] column is just numerical indexing based on [Weeks Bin] values.

Also [dispatch weeks] is a measure and has nothing to do with Stocks[Index]:

dispatch weeks =    // number of weeks covered by stock based on the previous 13-wk dispatch average)

    DIVIDE(

        SUM(Stocks[Qty]) + 0,

        [dispatch qty Rav]

    )

dispatch qty Rav =

VAR period =        // gets a period which ends the previous Saturday and extends back for 13 weeks

    DATESINPERIOD(

        DimDate[Date],

        MAX(DimDate[Date]) - WEEKDAY(MAX(DimDate[Date])),   // previous Saturday

        -7 * 13,                                                                                 // extend backwards 13 weeks

        DAY

    )

VAR total =             // totals the quantity over the 13 weeks period

    CALCULATE(

        [dispatch qty],

        period

    )

VAR numofweeks =        // number of weeks, will return < 13 if period hits date table starting date

    CALCULATE(

        ROUNDUP(

            DIVIDE( COUNTROWS(period), 7 ),

            0

        ),

    period

    )

RETURN

    IF(

        HASONEVALUE(Items[Item Code]),  // if one item is selected, calculate the dispatch quantity 13wk running average

        ROUNDUP(

            DIVIDE(

                total,

                numofweeks

            ),

            0

        ))

[AhmadBakr]

Thank you very much for sharing this important post. I have not tried applying it yet, nevertheless I felt obliged to thank you once I finished reading it.

Once I apply it and hopefully it resolves the issue, I will be commenting again and marking the reply a solution.

[ValtteriN]

Hi,

I recommend reading this article by SQLBI: Understanding circular dependencies in DAX - SQLBI

Basically your second calculated column is referring to [weeks bin] even if you don't explicitly state it.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[RockMan]

I THINK I managed to solve the problem, maybe… I followed the instructions from this video!: https://www.youtube.com/watch?v=CQHcZFk7pXc

In this way, I modified a previous formula that the new formulas used as follows:

Before:
"""
_Duration In Minutes =
SUMX(
'LTX',
HOUR('LTX'[Duration]) * 60 +
MINUTE('LTX'[Duration])
)
"""

After:
"""
_Duration In Minutes =
CALCULATE(
SUMX(
'LTX',
HOUR('LTX'[Duration]) * 60 +
MINUTE('LTX'[Duration])
),
ALLEXCEPT('LTX', 'LTX'[Video_ID])
)
"""

I’m still testing, but it seems to have worked. Hope it helps!

[RockMan]

I’m facing exactly the same issue. Earlier today, I updated Power BI to the August version, and I’m suspicious that it might be a bug. My formula is as follows:
"""

DurationGroups=

SWITCH(

    TRUE(),

    'Medidas'[_Duration In Minutes] <= 59, "Less than 1 hour",

    'Medidas'[_Duration In Minutes] <= 119, "More than 1 hour",

    'Medidas'[_Duration In Minutes] <= 179, "More than 2 hours",

    'Medidas'[_Duration In Minutes] <= 239, "More than 3 hours",

    'Medidas'[_Duration In Minutes] <= 299, "More than 4 hours",

    'Medidas'[_Duration In Minutes] <= 359, "More than 5 hours",

    "More than 6 hours"

)

"""
I also spent hours trying to find any correlation, but nothing. I really hope it’s not a bug but something I’m doing wrong. I don’t know what else to say. I’ll keep an eye out to see if anything comes up.

[AhmadBakr]

I tried changing the difinition of the [Index] to be independent of [Weeks Bin], still got the same error. In the below [Weeks Bin] is not included in the calc col definition, yest same circular ref error!!!!!!!!
Index =

    SWITCH(

        TRUE(),

        [dispatch weeks] <= 2 ,  1,

        [dispatch weeks] <= 4 ,  2,

        [dispatch weeks] <= 6 ,  3,

        [dispatch weeks] <= 8 ,  4,

        [dispatch weeks] <= 10,  5,

        [dispatch weeks] <= 13,  6,

        [dispatch weeks] <= 26,  7,

        [dispatch weeks] <= 39,  8,

        [dispatch weeks] <= 52,  9,

        [dispatch weeks] >  52, 10

    )

I deleted both cols, defined only Index, went fine, defined [Weeks Bin] using a different col name 🙂 [Bin], still got a circular reference error to [Index]: "A circular dependency was detected: Stocks[Bin], Stocks[Index], Stocks[Bin]."