Circular Dependency Issue Referencing Line Above

[achen]

Hi,

I am having issues with a ciruclar depency.

I have 5 colummns.

"Incoming" and "Capacity" are provided values.

The "Remainder" should just the the "Capacity" - "Work Required". If its ngeative, then it is 0.

The "Work Required" needs to be sum of the current weeks "Incoming" and the previous weeks "Remainder". That is where I am having issues. This is the one giving me issues

I have tried using a lookup for the previous weeks "Remainder value" to add to the current weeks "Incoming" but I keep getting a circular dependncy.

I am hoping for something like this:

Week	Incoming	Work Required	Capacity	Remainder
1	3	3	5	0
2	8	8	5	3
3	3	6	5	1
4	2	3	5	0

Can someone help me with how to get around that?

[lbendlin · Accepted solution]

To resolve circular dependencies in DAX you either need to completely rethink your approach, or push the calculation further upstream (into Power Query for example)  which will make the result static.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WMlTSUTIGYlOlWJ1oJSMgywLOM0aRMwGyjCC8WAA=", BinaryEncoding.Base64), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Week = _t, Incoming = _t, Capacity = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Week", Int64.Type}, {"Incoming", Int64.Type}, {"Capacity", Int64.Type}}
  ), 
  #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), 
  #"Added Custom" = Table.AddColumn(
    #"Added Index", 
    "Remainder", 
    each List.Accumulate(
      {0 .. [Index]}, 
      0, 
      (state, current) =>
        List.Max({state + #"Added Index"[Incoming]{current} - #"Added Index"[Capacity]{current}, 0})
    ), 
    Int64.Type
  )
in
  #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[achen]

My "Incoming" values actually come from another table and its calucalted through various formulas in DAX. I assume I would need redo all of that in power query as well?

Would there be any other way to get it done in DAX?

[lbendlin]

Your scenario is "Iteration with reset".  That is impossible to do in DAX.  Can only be done via List.Accumulate.