Refresh data of an underlying query based on a parameter or slicer filter

[Anonymous]

Hi,

I have built a Power BI app that looks at a single weeks performance and have created @StartDate parameter for the first day of the week.  The SQL query is fairly complex with several temp tables.  I then used the query to build the dataset which runs the app. It automatically runs the current week without the users having to do anything. They like it but now want to select previous weeks.  I can build a slicer filter with the StartDate weeks but what I need to do is to find a way to pass the StartDate to the underlying query and have it rerun the data.  The query is too complex to simply run multiple weeks and then filter on one week of results.  Is there a way to create a parameter that when clicked on will rerun the underlying query with a previous weeks results?

Thanks

[v-juanli-msft]

Hi @Anonymous

I'm afraid there is no direct option on a visual to meet your requirement.

However, here is a workaround you can take into consideration.

Create a history table which contains previous weeks results, then pass parameter in this table.

In the query editor, first copy the dataset, then in this copied dataset, you can right click on a query to select or deselect the option "Include In Report Refresh" and "Enable Load".

Then create a “history table” that store the history data, uncheck “"Include in Report Refresh" option so to disable the refresh to this table. You can refer to this article to achieve your goal better.

Best Regards

Maggie