Forum Discussion

rpinxt's avatar
rpinxt
Solution Sage
2 years ago
Solved

Circular dependency.....why???

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,       ...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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.

lbendlin's avatar
lbendlin
Super User
2 years ago

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's avatar
rpinxt
Solution Sage
2 years ago

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's avatar
    lbendlin
    Super User
    2 years ago

    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's avatar
      rpinxt
      Solution Sage
      2 years ago

      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.