Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anita9
Regular 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))

10 REPLIES 10
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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?

Anonymous
Not applicable

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

Yes to both.
Anonymous
Not applicable

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.

 

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.

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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?

Anonymous
Not applicable

Hi,

I am facing the exact same problem. I am using the same query as yours, but the default parameters are still not overwriten.

Can anyone help with this?

Many thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors