Forum Discussion
Issues with Query Parameters Needing to Refresh Data Source
Seems like an interesting issue. So, I assume that you have a project priority as well as a project cost, correct? And so is the end goal to get the best mix of projects for a given set price amount, correct? So, in other words, if priority is ranked low to high with the most important projects ranked lowest (1 = high priority, 100= low priority) then the goal is to calculate the lowest priority total whose project costs do not exceed the combined cost of those projects? Or is it easier than that and you just total up the cumulative costs of the projects in rank order until you hit the threshold?
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.