Simple Loop

[SammyNed]

Hi team,

Can you please assist? Im unsure if such a thing is possible in Powerbi, but can we do looping? Basically i have a table, sample data shown below, showing all the incomes per customer and their purchases. i would like to know what they are purchasing with their income and have an output table similar to that shown below. so we take the income value and then loop through every purchase until the expense value is =< the income value. 

Please let me know if this is possible?

Thanking you in advance

[v-jingzhang]

Hi @SammyNed 

I have a few questions. Can you please make them clearer? 

1. Does every customer have only one income value in the table? With only one income value, the problem will be easier. However, if a customer has multiple income values, we need to know from which date we starts to count the expenses. And we need to count based on the total income or on every single income?

2. According to the sample final result, do you only need to count the expenses happening after an income value and ignore the ones previous of that? 

Best Regards,
Community Support Team _ Jing

[smpa01]

@SammyNed  I am willing to give this a go, if you can please provide some sample data in a table format with the desired output clearly mentioned.

@AlexisOlson  this is a problem similar to maximizing subset for a given sum, which can be achieved by genrating all possible subset combinations and their corresponding sum in PQ.

I doubt if it can be achieved in DAX. 

[AlexisOlson]

@smpa01 You mean the multiple knapsack problem? If you can brute force in PQ, I can probably do it in DAX too but a brute force approach has exponential complexity and won't scale well beyond small sets. M is probably necessary for more efficient algorithms.

[AlexisOlson]

DAX doesn't really have loops. Everything is done with tables and filters. You can do most of the same stuff this way with the exception of certain kinds of recursive logic.