Forum Discussion
Advocate IIssues with Query Parameters Needing to Refresh Data Source
Advocate IThanks 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.
Community ChampionOK, 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.