Incremental Refresh Date Parameters

[jpt1228]

Hello - I want to get one of my data models on the incremental refresh. I have not used parameters before. From what I understand I need to go into the Query Editor and create 2 Parameters - DateStart and DateEnd (Or whatever you want to name them OR do they need to be called RangeStart and RangeEnd?). In the below example for Current Value it shows 1/01/2010 12:00:00 AM - Is this just a text field or does it need to be dynamic or formula driven?

Then I filter my data table on is after or equal to: Parameter DateStart and is before or equal to Parameter DateEnd. This returns a blank table becasue the DateStart and DateEnd current value is 1/1/2019 12:00:00 AM in both of my variables. I am not sure if this is normal and then the incremental refresh manages the parameters or what.

Maybe I don't even need to enter the parameters with the new release and just setup incremental refresh?

PBI Incremental Refresh 

[jpt1228]

Here is an additional explaination for the incremental refresh.

https://community.powerbi.com/t5/Community-Blog/Important-Considerations-for-Setting-up-Incremental-...

[v-kelly-msft]

Hi @jpt1228 ，

1.For Current Value，it is just a text field .

2.Verify queries have an equal to (=) on either RangeStart or RangeEnd, but not both. If the equal to (=) exists on both parameters, a row could satisfy the conditions for two partitions, which could lead to duplicate data in the model. For example,
#"Filtered Rows" = Table.SelectRows(dbo_Fact, each [OrderDate] >= RangeStart and [OrderDate] <= RangeEnd) could result in duplicate data.So you cant filter your data table on is after or equal to: Parameter DateStart and is before or equal to Parameter DateEnd.

Here is the reference.

Best Regards,
Kelly

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

[jpt1228]

Hello @v-kelly-msft  @parry2k  - I have read that post. So from what I understand the field is text, but am confused why there are formulas in the examples.

My 2 questions are:

1) Does the Paratemer need to be called RangeStart and RangeEnd? Or is this just a name of the parameter and has no impact on the functionality?

2) Then on the data table in the query editor I filter the specific column in the parameter to is on or after the start parameter and before the end parameter?

Then close and apply and select table I want to incremental refresh and select amount of time to keep and amount of time to refresh and then publish to the service?

I suppose just looking for some plain language rather than copy and paste from the post.

Thanks

[v-kelly-msft · Accepted solution]

Hi @jpt1228 ,

Answers for your questions are:

1.Yes, Paratemer needs to be called RangeStart and RangeEnd.

2.Yes,you did right.

I guess you have understood how to set the Incremental Refresh,just have a try,be sure that you do it in a Premium account.

Best Regards,
Kelly

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

[jpt1228]

Hello @v-kelly-msft  I have configured the data model to incremental refresh as noted above. When I publish to the service and try to refresh I get the following error:

You mentioned it needs to be a premium account but in the Feb update it is now included in the pro version.

Thanks

Jon

[parry2k]

@jpt1228 everything is so well explained in the post and not sure how better it can be explained. I would recommend to test it, just follow the instructions and I'm sure you will get your answer and after that if you are not sure then post your questions. 

[parry2k]

@v-kelly-msft not sure what you mean by text field.  you cannot just type random value there, if you have selected parameter type to be date/time then it has to be valid date/time value, not sure what you mean by text. Can you clarify?

[v-kelly-msft]

Hi @parry2k ,

As @jpt1228 asked " Is this just a text field or does it need to be dynamic or formula driven?",I just followed his understanding to say it is a text field,I just wanna tell him it is not formula driven or dynamic automatically.

But I know it's not a rigorous saying,thank you for correcting me.

 
Best Regards,
Kelly

[parry2k]

@jpt1228 this post has all the details. 

[parry2k]

@jpt1228 it is date/time as you can see in the post.