Forum Discussion
Issues with Query Parameters Needing to Refresh Data Source
Thanks for the reply!
1. They may change, but only annually or upon special request from the client. They are primarily 4 columns with max of 2,000 rows a piece.
2. The idea is that the end user will be able to edit the budget amount and watch the impact update over the 10 year period, letting them see the consequences of their decisions.
OK, a couple thoughts here. One, sounds like a great use case for "What If" parameters but you would need to use measures, not calculated columns for that to work correctly. Calculated columns recalculate at the time of load. Measures recalculate dynamically. Sounds like you should be using measures and not calculated columns.
That being said, another way to approach this would be to use an Enter Data query with columns for anything that you want them to be able to change and put all of your calculated columns in there. Then you would turn off refresh on all of your other queries and they could go into the Enter Data query, edit the Source to enter in a new budget number or whatever and all of your calculated columns would recalculate.
But, ultimately if you are refreshing the Excel data and do not want to have to ship them a new PBIX all the time then you would want to put your Excel files somewhere like OneDrive or something that you can share with them. Then, if you point your queries to such a path, it would work for you and them to refresh.
But, ultimately, it really really sounds to me like you should be using What If parameters and measures.
Ok, I've been able to get what I've built by managing what is refreshed, but my intent now is to rebuild using other measures and what if parameters. My biggest challenge is figuring out how to get a measure to show me all of the projects that will "fit" into my parameter amount. The projects are ranked by priority at the Query level.
So for the example below, columns C & D show the results I'm looking for - note that I want it to include projects that "fit" that may have higher priority projects that don't fit. Note that the cumulative total showing isn't important, just that the correct projects are easy for me to obtain and show elsewhere.
Is there a way to do this? Previously, I was using about 8 calculated columns, which obviously don't update without refresh
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.