Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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.
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?
Your scenario is "Iteration with reset". That is impossible to do in DAX. Can only be done via List.Accumulate.