Forum Discussion
Issues with Query Parameters Needing to Refresh Data Source
If I'm following you correctly, it's somewhere in between. To start with, it should just use a running total up to the point where the next sequential project doesn't fit (row 10 in column C and row 12 in column D of the picture I posted). Then, I need it to essentially skip down the ranking until a project "fits" into the remaining funds, update the new cumulative total, and then try to fit another project until successful or until no projects remain.
I've not compared the logic that you first described with this - I think that would produce different results.
Can you post your current DAX column formulas? The issue I forsee with what you are doing is essentially the "looping" aspect of it, which is not a strong suite for DAX.
Sure. I'm using several columns to do this currently. I've listed them and explanations below. Note that I have a different project list for each year, hence the '2028 in each column. The fastest way I found to create and update the columns is to copy these formulas to excel and use "find/replace" to change the year.
Initial Cumulative Total - a running total of initial acceptable projects that fit the budget amount
Initial Projects that Fit Budget - the project ID's for the previous column
Additional Projects that Fit - any additional non-sequential projects that fit the remaining budget
Intermediate Project Compiler - combines the previous two columns Project ID's - probably unnnecessary, but built when I first started out
Intermediate Cumulative Total - Cumulative Total of the Previous column's projects
Non-Sequential Projects that Fit - essentially the same as additional projects that fit the budget, but for this "loop" of the code - it's still possible for an acceptable project to fit through the budget, but given the project costs, it's very unlikely
Project Compiler - Combines all 3 columns of acceptable projects
Final Cumulative Total - essentially a logic check to ensure additional projects won't fit - probably unnecessary as well.
The goal is to get the final project list project ID's into a column.
Initial Cumulative Total = if('2028'[Allowable Projects]=1,if(SUMX(filter('2028','2028'[Final Rank]<=EARLIER('2028'[Final Rank])),'2028'[Cost])<=max(Parameters[Annual Budget]),SUMX(filter('2028','2028'[Final Rank]<=EARLIER('2028'[Final Rank])),'2028'[Cost]))) Initial Projects that Fit Budget = if(not(isblank('2028'[Cumulative Total])),'2028'[ProjectID]) Additional Projects that Fit Budget = if(ISBLANK('2028'[Initial Projects that Fit Budget])&&'2028'[Cost]<(max(Parameters[Annual Budget])-max('2028'[Cumulative Total]))&&'2028'[Allowable Projects]=1,'2028'[ProjectID]) Intermediate Project Compiler = if(not(ISBLANK('2028'[Initial Projects that Fit Budget])),'2028'[Initial Projects that Fit Budget],if(not(ISBLANK('2028'[Additional Projects that Fit Budget])),'2028'[Additional Projects that Fit Budget])) Intermediate Cumulative Total = if(SUMX(filter('2028','2028'[Final Rank]<=EARLIER('2028'[Final Rank])&¬(isblank('2028'[Potential Project Compiler]))),'2028'[Cost])<=max(Parameters[Annual Budget]),SUMX(filter('2028','2028'[Final Rank]<=EARLIER('2028'[Final Rank])&¬(isblank('2028'[Potential Project Compiler]))),'2028'[Cost])) Non-Sequential Projects that Fit = if(ISBLANK('2028'[Intermediate Cumulative Total]),if(max(Parameters[Annual Budget])-max('2028'[Intermediate Cumulative Total])>='2028'[Cost],'2028'[ProjectID])) Project Compiler = if('2028'[Allowable Projects]=1,if(not(isblank('2028'[Initial Projects that Fit Budget])),'2028'[ProjectID],if(not(ISBLANK('2028'[Additional Projects that Fit Budget]))&&NOT(ISBLANK('2028'[Intermediate Cumulative Total])),'2028'[ProjectID],if(not(isblank('2028'[Non-Sequential Projects that Fit])),'2028'[ProjectID])))) Final Cumulative Total = if(SUMX(filter('2028','2028'[Final Rank]<=EARLIER('2028'[Final Rank])&¬(isblank('2028'[Project Compiler]))),'2028'[Cost])<=max(Parameters[Annual Budget]),SUMX(filter('2028','2028'[Final Rank]<=EARLIER('2028'[Final Rank])&¬(isblank('2028'[Project Compiler]))),'2028'[Cost])) Also, if I'm doing something ignorant, please let me know.