Forum Discussion
Issues with Query Parameters Needing to Refresh Data Source
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.