topic Re: Strange! Unclear circulrar reference in DAX Commands and Tips

Strange! Unclear circulrar reference

AhmadBakr — Thu, 22 Aug 2024 07:52:22 GMT

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

period

)

RETURN

IF(

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

ROUNDUP(

DIVIDE(

total,

numofweeks

))

Re: Strange! Unclear circulrar reference

AhmadBakr — Thu, 22 Aug 2024 08:07:02 GMT

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

Re: Strange! Unclear circulrar reference

ValtteriN — Thu, 22 Aug 2024 08:07:59 GMT

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/

Re: Strange! Unclear circulrar reference

AhmadBakr — Thu, 22 Aug 2024 12:52:49 GMT

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.

Re: Strange! Unclear circulrar reference

RockMan — Thu, 22 Aug 2024 20:27:53 GMT

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.

Re: Strange! Unclear circulrar reference

RockMan — Thu, 22 Aug 2024 20:58:13 GMT

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!