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 II don't believe it is as hopeless a situation as you describe.
I was able to make some progress - though I admit I still don't have the full solution.
After calculating the demand referred out, and calculating the demand remaining at the PSA, I have a measure for the demand referred in for each PSA as follows:
This resolves the first issue I originally posted about. I then took it a step further, by simply writing another measure that adds the Referred In demand and the "Adjusted Demand" - which probably isn't the best name for the field but it represents the demand originated at the PSA minus the portion that was referred out.
I think of this as the first cycle of referral. For any PSA that receives no Referred In demand, this is the end of the calculation but the problem is that some PSAs continue to refer their new adjusted demand onto another location. And the important part to remember is that it may not always be sending on 100% of it's demand.
To calculate this, I wrote a measure that subtracts the demand that the PSA already referred out from its Adjusted Demand, and then multiplie by the referral percentage. I subtract the referred out demand from the first cycle first so that I don't double dip on the referral percentage. Also, some PSAs will have already referred out 100% of their demand, so the subtraction will result in a negative number. I handle that with a conditional statement that just returns zero.
The measure looks like this:
This is the new demand that each PSA has to send on to another PSA for the second cycle.
To get to the demand at the end of the second cycle of referral, I have a measure like this:
I get the sum of all of the demand I calculated for the second cycle, and add it to the demand calculated in at the end of the first cycle, but only for each PSA where it is programmed not to refer demand on to any location other than its-self (this is the default setting for PSA referral locations if they are not referring demand on). If the PSA dose have a referral location, then I subtract this second cycle of referred out demand from the first cycle of demand.
This solution with a series of measures works. However, it is predicated on the fact that at most there are 2 cycles of referral. Either the demand goes from A to B, or from A to B to C.
I am certain there has to be a way to combine all of these measures into a single calculation - someone better than me in writing DAX could figure out... Then, if I could just have the measure understand the referral pattern first... If I could calculate the number of referrals/relationships... that would be the final solution.