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 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:

 

WeekIncomingWork RequiredCapacityRemainder
13350
28853
33651
42350

 

 

 

 

Can someone help me with how to get around that?

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

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    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.

    • achen's avatar
      achen
      Frequent Visitor
      2 years ago

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

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