Forum Discussion
Circular Dependency Issue Referencing Line Above
- 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.
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.
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?
- lbendlin2 years agoSuper User
Your scenario is "Iteration with reset". That is impossible to do in DAX. Can only be done via List.Accumulate.