Reply
Highlighted
Frequent Visitor
Posts: 11
Registered: ‎09-26-2018

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 with sending a PBIX file to the client.  I've looked at a ton of other posts on here and elsewhere, and I think I'm stretching PBI's functionality a little bit, as most people aren't doing what I'm doing.  To help those who may help me, I'm going to try to explain the different layers this issue goes down into.

 

Primary Issue: Unlike Tableau, which allows me to create a packaged workbook that I can send to a client that they can easily open, I've not found any similar functionality in PBI.  If that's not the case, how could I do this?

 

So, in order to send the few page report (I think that's what it's called) to the client, I either need to send them the dataset, which is 22 different spreadsheets (this could probably be condensed to one if needed), which I would then have to re-establish the linkage to the dataset on his machine.  Or, I can try to go down the Sharepoint route, which I've started to investigate, but it seems like on this route, Query Parameter changes don't completely recalculate all calculated columns, as well as being complicated (needing to set up a gate, etc) and not super reliable (based on other forum posts which may or may not be true).  The recalculation issue is why I’m using Query Parameters instead of “What If” Parameters under the Modeling tab in the first place.  It seems like clicking refresh doesn’t update calculated columns if you use the “What If” parameters, only the query parameters.  If this isn’t true, again, please point me in the right direction.

 

Essentially, my client has a large list of projects and a limited annual budget for the next ten years.  The project list will change (get worse) and lengthen (add projects) each year.  They are wanting to rank the projects according to a condition index in a specific way, using two different thresholds (Not super important to explain I don’t think, but was really difficult to figure out as my first real dax command as it involved different rank orders above and below the thresholds).  Then, once ranked, they want to be able change the annual budget amount and see how that affects the 10 year project outlook.  This involves removing projects that were previously completed at the current budget level.  Then, they want to show a breakdown of the executed projects as well as the uncompleted projects on a yearly basis, with the goal being finding the budget amount that lets them work through project list over 10 year time frame. 

 

What happens when I share the file is that the user then clicks the refresh button to update the data, but because the linkages are broken, the refresh freezes and can’t complete.  Do I need to import the data in a separate table to the calculations? Will this actually fix the problem?  I’ve been slow to try this as it would require a lot of rework.  It would go a lot faster if PBI had a find/replace function, but I’ve not found that either.

 

Soooo, I’m stuck.  An intermediate solution I’ve thought of is to put the PBIX file on a hard drive with the dataset and reference the dataset on that drive.  However, this limits his ability to share the tool inside his organization, which is something he’d needs to do.

Please share any thoughts, directions or solutions you may have.  Working with an NDA, so I can’t share a file but I might be able to share pictures if I

Super User
Posts: 10,448
Registered: ‎07-11-2015

Re: Issues with Query Parameters Needing to Refresh Data Source

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?

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Frequent Visitor
Posts: 11
Registered: ‎09-26-2018

Re: 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.  

Super User
Posts: 10,448
Registered: ‎07-11-2015

Re: Issues with Query Parameters Needing to Refresh Data Source

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Frequent Visitor
Posts: 11
Registered: ‎09-26-2018

Re: Issues with Query Parameters Needing to Refresh Data Source

[ Edited ]

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

 

Scenario.PNG

Super User
Posts: 10,448
Registered: ‎07-11-2015

Re: 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?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Frequent Visitor
Posts: 11
Registered: ‎09-26-2018

Re: Issues with Query Parameters Needing to Refresh Data Source

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.

Super User
Posts: 10,448
Registered: ‎07-11-2015

Re: 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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Frequent Visitor
Posts: 11
Registered: ‎09-26-2018

Re: Issues with Query Parameters Needing to Refresh Data Source

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])&&not(isblank('2028'[Potential Project Compiler]))),'2028'[Cost])<=max(Parameters[Annual Budget]),SUMX(filter('2028','2028'[Final Rank]<=EARLIER('2028'[Final Rank])&&not(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])&&not(isblank('2028'[Project Compiler]))),'2028'[Cost])<=max(Parameters[Annual Budget]),SUMX(filter('2028','2028'[Final Rank]<=EARLIER('2028'[Final Rank])&&not(isblank('2028'[Project Compiler]))),'2028'[Cost]))
Frequent Visitor
Posts: 11
Registered: ‎09-26-2018

Re: Issues with Query Parameters Needing to Refresh Data Source

Also, if I'm doing something ignorant, please let me know.