Changing a variables and stored values based on looping conditions that compares 2 objects?

[Anonymous]

Is it possible to create a loop that checks a condition and then applies new values to the original variables?

More specifically I have cross-joined a table to compare elements against each other and based on that first comparison they are then compared again and their values are reduced until one of the two comparisons hits zero which will then do an If statement to label the “winning” element of the two based on the percentage of its starting value that remains for the “winner”.

Here is an example of the data:

Elements to Compare Numeric Value Change Starting Value

Obj	Change	Value
AW	.75	6
CR	4.44	40

I’d need the above info do the following

1. Compare each element against each other to be placed in a matrix with your element of choice on the first column and the opposing element as the headers of each other column. Each cell between them will initially look at the “starting value” minus the “numeric value change” and return the result.

   1. E.g. AV with a starting value of 6 is compared to CR with a Numeric Value Change of 4.44 the compared value for “round 1” is 6 - 4.44 displaying 1.56 as the remainder going into “round 2”.

   2. CR would also have its starting value of 40 and subtract the Numeric value Change from AW (.75) leaving the “Round 2” starting value for CR as 40 - .75 at 39.25 starting value for CR.

2. It will then take that results from “Round 1” and then alter the starting values to subtract the numeric value change again.

   1. Same elements would now be AW vs CR but AW = 1.56 Starting value instead of 6 and CR’s new starting value would be 39.25

   2. It will run through the calculations again 5 times or until one of the two elements hit’s 0 first.

      1. AW Starting Value vs CR Numeric Value Change = 1.56 – 4.44 resulting in AW new starting value for round 2 stored as -2.88.

      2. CR Starting Value vs AR Numeric Value Change = 39.25 - .75 resulting in CR new starting value of 38.5

   3. Since Round 2 ends with AW “starting value” as less than 0 the loop would end and then declare CR the standing element at (New Starting Value of 38.5/Original Starting Value of 40)*100 or at 96.25% remaining “Starting Value.

3. As a measure it could then run this comparison in the matrix after cross-joining the duplicated tables unless someone has a better alternative for that as well?

Big goal here is knowing if I can loop the formula and update the variables for “Starting” values while pulling in the variable for “Numeric Value Change” which may or may not have that value changed as the “starting value decreases over time or “Rounds”

Can we update variables with new values and step through or loop to continue calculating the result until a final value of 0 for one of the two compared elements is achieved or 5 rounds of comparisons have passed?

[Cmcmahan]

So while this it is likely possible to create such a table with DAX, it's going to be a messy process.  DAX is not a tool built for easy row-by-row calculations.  It gets even more complicated because it looks like you'll want to swap different objects in at will

Before you start trying to craft this, answer these questions:

Do you actually need all the intermediate step results?
Do you just need the final outputs? 
Is there a better program for doing this that you could feed into PowerBI?

How would this look in pseudo-code?  If you start using loops, DAX probably isn't the best tool you could be using.

[Anonymous]

Ah got it, after posting this I saw some threads about the challenges of DAX and iteration, maybe Python may be better suited for this within Power BI? 

I am still fairly new to the world of programming but willing to give it a shot if that's a route within Power BI and Python.

As I understand it this would be best suited as while loop for obj1 or obj2 being greater than zero and calculate each iteration that changes the variable holding the 'value' by the 'change' which then effects the 'change' variable as well since the 'change' value is calculated in part by some of it's 'value' each step as it decreases.

Another person I spoke to described it as if two boxers were taking turns punching each other, each punch would bring one of them closer to knocking the other out but it would also cause each boxer fatigue each punch making the successive punches weaker. Odd analogy perhaps but I hope it helps with describing why I am trying to go through this challenge to factor in the interactions of the objects being compared over iterations.

[Cmcmahan]

So I was thinking about this, because it seemed like there needed to be a better way (mathematically) to get to the final answer you need, without going through the effort of creating a table.

If I understand correctly, the "Change" for each of the two Objects is always the same, and you're always subtracting it from the current "Value".   If  this is the case, you just want to do some division, round up to the nearest whole number, and compare the results.  There are some edge cases you need to account for, like when either Object lasts more than 5 "rounds", and who wins ties, but here's the basic DAX code to get you closer to your answer:

BetterObject = 
VAR firstObjectScore = ROUNDUP( DIVIDE(Obj1[Value], Obj2[Change]), 0 )
VAR secondObjectScore = ROUNDUP( DIVIDE(Obj2[Value], Obj1[Change]), 0 )
RETURN
IF( firstObjectScore < secondObjectScore, Obj1[Obj]&" is the winner", Obj2[Obj]&" is the winner")

You need to figure out some way to indicate which row Obj1 is and which one Obj2 is, and this doesn't do your % change math, though that could be used in place of the "____ is the winner" text.  If Obj1 "won" would be done by taking Obj1[Value] - secondObjectScore * Obj2[Change].

Hopefully this pushes you in the right direction.  I'll leave you to the exercise of figuring out a way to refer to which row of your fact table is "Obj1" and which is "Obj2".  If you need help on this after playing with it for a bit, feel free to respond to this thread.