Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AhmadBakr
Advocate II
Advocate II

Strange! Unclear circulrar reference

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
        ))
5 REPLIES 5
AhmadBakr
Advocate II
Advocate II

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
Super User
Super User

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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!

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
Advocate II
Advocate II

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]." 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.