Incremental refresh - not working?

[vmakhija]

Hello,

I am implementing an incremental refresh for one of my reports.

Here are the steps I did -

1. Created parameters for RangeStart and RangeEnd with their values as 6/1/2020 and 12/31/2020 (these dates so as to not make my PBIX file huge)

2. I filtered rows for my query to pull data from RangeStart to RangeEnd.

3. After Close & Apply, it gives me data starting June 1st till date.

4. I configured incremental refresh for this query to pull data for last 3 years and refresh for last 1 day only.

5. After I published this report to Service and refreshed it (on demand), it gives me data from 6/1/2020 although I do have data starting 2018 in my SQL server database.

So, I wonder what part of configuation is not working as this seems to be quite straight forward to do.

Any inputs?

Regards

[amitchandak]

@vmakhija , Have you configured on-premise gateways

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem

Also, open the dataset on power bi service check for credential and check the need of scheduled refresh

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data

[parry2k]

@vmakhija Range Start and End should be date/time columns

@amitchandak not sure what has a gateway to do with it, data is getting refreshed but seems like the first full load is not happening.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[vmakhija]

@parry2k 

@amitchandak 

Both RangeStart and RangeEnd are DateTime parameters. My corresponding column on which I put filter is also the datetime column.

My source is Azure SQL, so won't need a gateway. Anyway, refresh is working fine, but doesn't do a full refresh.

It pulls data from June 1st only and should get data from 2018.

Regards

[parry2k]

@vmakhija quick question, are you using import or direct query?

[vmakhija]

@parry2k 

I am using Import.

Below is my query which does indicate the query folding happening 

However, in the configuration, it says it is unable to confirm if Query folding is happening or not, so that is weird.

Regards

[v-yingjl]

Hi @vmakhija ,

1.  If your data source coming from a data source that supports query folding, then Incremental load works as it best. If not, it is not recommended to use it. Most of the time, a huge data source is coming from a relational data store system, which supports query folding by the way. Refer: All You Need to Know About the Incremental Refresh in Power BI: Load Changes Only 
2. If the PBIX file was authored in a version of Power BI Desktop prior to the 2019 February release, the warning will be displayed regardless until a data refresh is performed. Refer: Incremental refresh & query folding 
3. Please check if there's filter logic in your query. It's recommended that verification is performed to ensure the filter logic is included in the source queries. To make this easier, Power BI Desktop attempts to perform this verification for you. If unable to verify, a warning is displayed in the incremental refresh dialog when defining the incremental refresh policy. SQL based data sources such as SQL, Oracle, and Teradata can rely on this warning. Other data sources may be unable to verify without tracing queries. If Power BI Desktop is unable to confirm, the warning is displayed.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[vmakhija]

@v-yingjl 

Thanks for your comments

My source is Azure SQL and in Power Query, I can see "Native Query" enabled, so not sure why Query folding does not happen.

Any reason why it is still not working?

Regards

[parry2k]

@vmakhija that's what I thought that full load is not working, can you share the setting where you have configured the incremental refresh.