Circular dependency.....why???

[rpinxt]

So I have this calculated column returning a nummber :

HourDiff =

VAR _StartDay = SC2[D1]

VAR _EndDay = SC2[D2]

VAR _Correction =

    IF(_EndDay = 1 && _StartDay < 6 && _StartDay <> 1,

        48,0)

RETURN

CALCULATE(DIVIDE(DATEDIFF(MAX(SC2[DT101]),MAX(SC2[DT311]),MINUTE),60),WEEKDAY(SC2[DT311],2) <6) - _Correction

So the output is just a number like 22,33455456 or 26,58948934

Now I want to make a simple calculated column which returns "Late" when the number is > 24 and else "On Time" with this :

Timing =

    IF(SC2[HourDiff] > 24, "Late", "On Time")

Why would such a simple IF return a circular dependency?

Better yet...how to avoid it....

[aniaquen232]

A circular dependency error typically occurs when a calculated column references another calculated column which in turn references the original column, creating a loop. In your case, the Timing calculated column references the HourDiff calculated column, which itself references other columns in the table, including SC2[DT311].

To avoid the circular dependency, you can rewrite the Timing column without directly referencing the HourDiff column. Here's a modified version:

DAX

Timing = IF( CALCULATE(MAX(SC2[HourDiff])) > 24, "Late", "On Time" )

In this version, we're using the CALCULATE function to evaluate the maximum value of the HourDiff column within the current context. This avoids the circular reference issue, allowing you to determine whether the value exceeds 24 and return "Late" or "On Time" accordingly.

[rpinxt]

Ok officially am losing my mind now 😂
So this was the logic in field 'Timer' that led to the circular:

Timing =

VAR _HourDiff = CALCULATE(DIVIDE(DATEDIFF(MAX(SC2[DT101]),MAX(SC2[DT311]),MINUTE),60))

VAR weekend = CALCULATE(COUNTROWS(dimDate),DATESBETWEEN(dimDate[Date],SC2[DT101],SC2[DT311] -1),dimDate[IsWorkingDay] = FALSE(),ALL(dimDate))

VAR correction = IF(weekend > 1, 48, 0)

RETURN

IF(_HourDiff - correction > 24,"Late","On Time")

Now I copy it over with the logic in the sample file :

Timing =

VAR _HourDiff = CALCULATE(DIVIDE(DATEDIFF(MAX(SC2[DT101]),MAX(SC2[DT311]),MINUTE),60))

VAR weekend = CALCULATE(COUNTROWS(dimDate),DATESBETWEEN(dimDate[Date],SC2[DT101],SC2[DT311] -1),dimDate[IsWorkingDay] = FALSE(),ALL(dimDate))

VAR correction = IF(weekend > 1, 48, 0)

RETURN  

IF(_HourDiff - correction > 24,"Late","On Time")

And of course....gone is the circular and also the sigma changed into an fx....

Perhaps I am looking already to long at this, but to me that is twice the same logic.

So why the orginal one was not working beats me....

Anyway all good now @lbendlin . Somehow it is al working now.

Thanks!

[rpinxt]

@lbendlinoohhh now I see what changed in your version :

This was :

You started at February so it could not see a weekend marker for January data so that is why your measure did show 0 for weekend in January.

But the determination of the max and min date than are the cause of the circular dependancy?

That would be strange.

[lbendlin]

When two tables depend on each other you always get a circular dependency. Nothing strange here.

[rpinxt]

Well I did my MAX and MIN in the sample file and the weekends where good again and no circular in the sample file....

But now I saw this :

In the sample file the Timing calculated column has an fx sign.

In the original :

There is is a sigma sign....

So how did you get that fx sign in the sample file @lbendlin ?

[rpinxt]

Hello @lbendlin and others.

I uploaded the pbix file to google drive.

This link should be public. Please let me know if not :

https://drive.google.com/file/d/1WNe7cZKkOawjx8RzxxfdKiGGVmUGkJHr/view?usp=sharing

So the problem is with the calculated colun 'Timing' in table SC2

[lbendlin · Accepted solution]

Your calendar table is derived from your fact table.  That is akin to using CALENDARAUTO, and is Very Bad Indeed.

Overcome the urge to create calendar scripts in DAX or Power Query. They are useless.  Use an external reference table.

You also don't need to specify table names in calculated columns.

[rpinxt]

Thanks @lbendlin , so autocalendar or calendar tables based on your source data are a bad idea?
What would you mean with an external reference table? Like an excel file with all dates of the last 5 years for example?

But if I understand correctly and looking at what you changed the field 'weekendcorrection' was the cause of the circular?

Your solution indeed does not lead to a circular dependency but it seems not to be correct:

First line in is 5 Jan 24 and 8 Jan 24. That was a Friday and a Monday so there was a weekend on that line.

You logic returns 0 hours there instead of 48. So that is not correct.

Is there something we can change in the logic to make it work (and not get a circular again)?

ps: thanks for pointing out that you don't need to specify table names in calculated columns.

[lbendlin]

Yes, an Excel file on a SharePoint will be good.

Sorry about messing with the formula. I didn't understand what you were trying to do there. Most likely not needed anyway as you can mark weekends and holidays in the calendar table.

[rpinxt]

Well I had a weekend "mark" in my autocalendar:

"IsWorkingDay", NOT WEEKDAY([Date]) IN {1,7},

Thought that that would be the problem but I see that you also used this field in your calculation:

WeekendCorrection =

var d1 = [DT101]

var d3 = [DT311]

VAR weekend = CALCULATE(

        COUNTROWS(dimDate),DATESBETWEEN(dimDate[Date],d1,d3 -1),dimDate[IsWorkingDay] = FALSE(),ALL(dimDate)

)

RETURN

IF(weekend > 1, 48, 0)

And now it does not result in dependancy but it also does not see the weekends.

Bit strange.

[lbendlin]

Yeah, weird.  You can avoid it by re-implementing the calculation rather than referencing it.  

[rpinxt]

You mean like putting the initial calculation in a VAR @lbendlin ?

Tried it (also changed my initial calculation but still no luck) but also does not work:

So I now made a column WeekendCorrection which returns 0 or 48.

But as you can see it still sees a circular dependency and I have no clue why....

Also why is it naming SC2[HourDiff]? That field is not involved in these calculations.

var _HourDiff is and not the original [HourDiff].

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.