cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anita9 Frequent Visitor
Frequent Visitor

Incremental refresh not overwriting parameters

I set everything up with a filter step converting our date key but when I publish it, it doesn't overwrite the default RangeStart and RangeEnd parameters.  The data in Power BI service ends up filtered to the default.

 

This is what I used for the conversion/filter step:

= Table.SelectRows(#"Source", each [REQUESTED_DATE_KEY] >= Date.Year(RangeStart)*10000+Date.Month(RangeStart)*100+Date.Day(RangeStart) and [REQUESTED_DATE_KEY] < Date.Year(RangeEnd)*10000+Date.Month(RangeEnd)*100+Date.Day(RangeEnd))

9 REPLIES 9
Community Support Team
Community Support Team

Re: Incremental refresh not overwriting parameters

HI @anita9,

 

If you mean preview feature incremental refresh which based on query parameters, it not support dynamic overwriting parameter, you need to manually modify them.

 

Incremental refresh in Power BI Premium

Override effective date
We plan to allow setting the current date for a refresh operation. This will be useful to use with datasets like Adventure Works that don't have data up to the current date, and for testing purposes.

 

BTW, you can also add filter in query edit and manually modify formula to achieve dynamic filters based on lastdate or local date.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
anita9 Frequent Visitor
Frequent Visitor

Re: Incremental refresh not overwriting parameters

I'm probably using the wrong terminology.  When I created the RangeStart and RangeEnd parameters, I used 6/1/2018 for RangeStart and 6/5/2018 for RangeEnd.  All of the documentation I've been able to find seems to suggest that it doesn't matter what you put in as the default because once you publish and refresh in Power BI Service, it will refer to the incremental refresh policy to overwrite the default for those parameters and will pull in all of the data that you select to keep (in my case, 4 years) and on subsequent refreshes will refresh what you select to refresh (in my case 5 days).

 

When I publish and refresh, only those five days from my default parameter filter are there.  In the dataset management area, under "Parameters" it shows my default dates and allows me to change them.  In other posts, it seems like this should be disabled.

 

It is giving me neither the 4-year history nor the latest 5 days update.  Are you saying that I need to manually adjust the parameters each day to the last 5 days?  It would give me the latest 5 days, but I still wouldn't have the 4-yr history, right?

Super User
Super User

Re: Incremental refresh not overwriting parameters

After you set up the parameters and did your "Apply and Close".  Did you right click on the tables in the Field Picker and select "Incremental Refresh"?  Also just to double check, did you upload this into a Workspace that has premium assigned to it (the diamond symbol)?

 

Capture.PNG


   

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

Proud to be a Datanaut!


   


anita9 Frequent Visitor
Frequent Visitor

Re: Incremental refresh not overwriting parameters

Yes to both.
Super User
Super User

Re: Incremental refresh not overwriting parameters

This was the method I used, perhaps you can give it a test.

 

This is the line immediately after the Source line:

 

    #"Incremental Refresh" = Table.SelectRows(Source, each [Period Start Date] >= RangeStart and [Period Start Date] < RangeEnd),

Naturally you'd need to change "Period Start Date" to your date field.

 


   

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

Proud to be a Datanaut!


   


anita9 Frequent Visitor
Frequent Visitor

Re: Incremental refresh not overwriting parameters

My table uses a date key instead of a date/time date, so I had to convert.  This is my filter step:

 

= Table.SelectRows(#"Source", each [REQUESTED_DATE_KEY] >= Date.Year(RangeStart)*10000+Date.Month(RangeStart)*100+Date.Day(RangeStart) and [REQUESTED_DATE_KEY] < Date.Year(RangeEnd)*10000+Date.Month(RangeEnd)*100+Date.Day(RangeEnd))

 

It seems to be the same as what you suggested but with the conversion.  I am able to get to the Incremental Refresh screen.  I publish it, refresh it, and it is still filtered to the 5 days that I used as defaults in the RangeStart and RangeEnd parameters.

Super User
Super User

Re: Incremental refresh not overwriting parameters

Take a screenshot of the incremental refresh screen, the one i posted.  I'm curious if something there might give me a clue.


   

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

Proud to be a Datanaut!


   


anita9 Frequent Visitor
Frequent Visitor

Re: Incremental refresh not overwriting parameters

Here it is.  One difference I noticed was that mine says "Store rows in the last:" and yours actually uses the column name.

 

Capture.JPG

Super User
Super User

Re: Incremental refresh not overwriting parameters

I think you have hit on the issue.  In my successful attempts, the data column was exactly that.  A column that just held Dates or DateTimes.  Looking at your original post, you have a fairly complex formula trying to build a date and do some logic around it.

 

Is it possible to have or create a date column, then apply your RangeStart and RangeEnd similar to the code I provided?


   

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

Proud to be a Datanaut!