Forum Discussion
Advocate ISUMX returning blanks (BonusPoints: avoiding circular logic)
Super User
a way to do that more efficiently, without multiple measures and without running into circular logic
- not really
- no
- heck no.
This is a closed loop control system that will require multiple runs to calm down and level in. There is no chance at all that a deterministic single loop system like Power BI (which doesn't even have the concept of global variables) can handle this very complex process.
Can you add a timing component? Datetime stamps on when the referrals happened? So you can build a sequence?
I guess you could build a solution out of hydraulics, or with mycelium.
Advocate Ilbendlin, I made some progress yesterday.
I didn't know DAX had functions to measure parent/child relationships. I can leverage these to detect exactly how many PSAs away a given PSA may be on the chain of referral. PSAs at the beginning of the chain with no children will have the largest path length. PSAs at the end of the chain with the most children and/or are only referring to themselves have the shortest path length.
So the larger the path length, the earlier its demand should be calculated. The PSA(s) with the smallest path length are calculated last.
Next, I need to write a looping statement... which I guess isn't possible in DAX but thankfully Greg_Deckler provided us with a write-up on how to work around that. I don't have the code written out yet but I think I have a map for how to get there.
- lbendlin
PATH only works if each element appears in only one place of the hierarchy. From your original description it sounded more like you have circular references between PSAs?
The FOR and WHILE constructs you reference will only work for independent computations in each of the loops. As soon as one loop iteration depends on the result of another iteration this will break down. There is a Power Query function List.Accumulate that is a bit more powerful but even that cannot mask the fact that Power BI does not have a concept of global variables.
I think your best chance is still when you provide additional sequencing information (ideally timestamps, or index columns).