Forum Discussion

JTPorterfield's avatar
JTPorterfield
Advocate I
7 years ago

Issues with Query Parameters Needing to Refresh Data Source

Hi there, fairly new PBI user here - proficient at Tableau, but learning PBI at a client's request.  All self-taught, no formal training, working in PBI for about 2.5 weeks.   I'm having trouble wi...
Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

OK, there's a lot going on in this post. Let's start with requirements as there are multiple ways of going about this.

 

 Among the options here are:

  • Share PBIX
  • Publish to Service and Share
  • Publish to Report Server
  • Publish to Web
  • Power BI Template File (PBIT)

Probably some more that I am forgetting about at the moment. But let's start with the basics:

 

  1. You have 22 spreadsheets that you have imported into a data model in a PBIX file. Are those spreadsheets going to change over time? Who is updating those spreadsheets?
  2. Does the end user just need to view the reports that are created or are they supposed to edit them?

 

  • JTPorterfield's avatar
    JTPorterfield
    Advocate I
    7 years ago

    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.  

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      7 years ago

      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.

      • JTPorterfield's avatar
        JTPorterfield
        Advocate I
        7 years ago

        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