Forum Discussion

achen's avatar
achen
Frequent Visitor
2 years ago
Solved

Circular Dependency Issue Referencing Line Above

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

    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.