Strange! Unclear circulrar reference

Question

I have 2 calculated columns:Weeks Bin =&nbsp; // calc col to order dispatch weeks in bins to prepare for a histogram&nbsp; &nbsp; SWITCH(&nbsp; &nbsp; &nbsp; &nbsp; TRUE(),&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 2 , "≤ 2" &nbsp;,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 4 , "2-4" &nbsp;,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 6 , "4-6" &nbsp;,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 8 , "6-8" &nbsp;,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 10, "8-10" ,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 13, "10-13",&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 26, "13-26",&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 39, "26-39",&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 52, "39-52",&nbsp; &nbsp; &nbsp; &nbsp; "&gt; 52"&nbsp; &nbsp; )Index =&nbsp; // calc col to introduce a parameter for sorting the bins as they are not numericalSWITCH(&nbsp; &nbsp; Stocks[Weeks Bin],&nbsp; &nbsp; "≤ 2" &nbsp;, &nbsp;1,&nbsp; &nbsp; "2-4" &nbsp;, &nbsp;2,&nbsp; &nbsp; "4-6" &nbsp;, &nbsp;3,&nbsp; &nbsp; "6-8" &nbsp;, &nbsp;4,&nbsp; &nbsp; "8-10" , &nbsp;5,&nbsp; &nbsp; "10-13", &nbsp;6,&nbsp; &nbsp; "13-26", &nbsp;7,&nbsp; &nbsp; "26-48", &nbsp;8,&nbsp; &nbsp; "48-52", &nbsp;9,&nbsp; &nbsp; "&gt; 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]."&nbsp;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.&nbsp;Also [dispatch weeks] is a measure and has nothing to do with Stocks[Index]:dispatch weeks =&nbsp; &nbsp;&nbsp;// number of weeks covered by stock based on the previous 13-wk dispatch average)&nbsp; &nbsp; DIVIDE(&nbsp; &nbsp; &nbsp; &nbsp; SUM(Stocks[Qty]) + 0,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch qty Rav]&nbsp; &nbsp; )&nbsp;dispatch qty Rav =VAR period = &nbsp; &nbsp; &nbsp; &nbsp;// gets a period which ends the previous Saturday and extends back for 13 weeks&nbsp; &nbsp; DATESINPERIOD(&nbsp; &nbsp; &nbsp; &nbsp; DimDate[Date],&nbsp; &nbsp; &nbsp; &nbsp; MAX(DimDate[Date]) - WEEKDAY(MAX(DimDate[Date])), &nbsp; // previous Saturday&nbsp; &nbsp; &nbsp; &nbsp; -7 * 13,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;// extend backwards 13 weeks&nbsp; &nbsp; &nbsp; &nbsp; DAY&nbsp; &nbsp; )VAR total = &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // totals the quantity over the 13 weeks period&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; [dispatch qty],&nbsp; &nbsp; &nbsp; &nbsp; period&nbsp; &nbsp; )VAR numofweeks = &nbsp; &nbsp; &nbsp; &nbsp;// number of weeks, will return &lt; 13 if period hits date table starting date&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; ROUNDUP(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DIVIDE( COUNTROWS(period), 7 ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; period&nbsp; &nbsp; )RETURN&nbsp; &nbsp; IF(&nbsp; &nbsp; &nbsp; &nbsp; HASONEVALUE(Items[Item Code]), &nbsp;// if one item is selected, calculate the dispatch quantity 13wk running average&nbsp; &nbsp; &nbsp; &nbsp; ROUNDUP(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DIVIDE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; total,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; numofweeks&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; ))

valtterin · Answer

Hi,I recommend reading this article by SQLBI:&nbsp;Understanding circular dependencies in DAX - SQLBIBasically 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/

ahmadbakr · Answer

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 =&nbsp; &nbsp; SWITCH(&nbsp; &nbsp; &nbsp; &nbsp; TRUE(),&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 2 , &nbsp;1,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 4 , &nbsp;2,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 6 , &nbsp;3,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 8 , &nbsp;4,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 10, &nbsp;5,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 13, &nbsp;6,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 26, &nbsp;7,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 39, &nbsp;8,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &lt;= 52, &nbsp;9,&nbsp; &nbsp; &nbsp; &nbsp; [dispatch weeks] &gt; &nbsp;52, 10&nbsp; &nbsp; )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]."&nbsp;

ahmadbakr · Answer

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.

rockman · Answer

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.

rockman · Answer

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!

Forum Discussion

Strange! Unclear circulrar reference

5 Replies

Related content

Export data strange characters

Strange CLOSINGBALANCE behaviour

dynamic reference line

Azure Key Vault References

referer (referring) URL in usage stats?