Incremental Refresh Not Loading Archive Partition

[genehsu]

I set up incremental refresh in desktop - parameters, filtering the query, and the policy on the main fact table in our model.  It refreshes quickly in desktop to pull just the Incremental Refresh partition.  However, when I publish to service and perform the initial refresh, it finishes quickly and only has the incremental data but did not pull in the 1 year of historical archived data.  Any thoughts on what could be causing it?

[genehsu]

I was able to get the incremental refresh working.  Everything was set up correctly; however, there was an append query in my model that also used the query I was trying to do the incremental refresh on.  I needed to set up incremental refresh on both queries in order for it to work properly.

[PshemekFLK]

@geneshu,

Can you elaborate more on how you fixed this? I have 3 tables which I'm filtering with the RangeStart and RangeEnd parameters and then appending all 3 into one fact table. How would I incorporate the parameters in the appended table?

[v-yueyunzh-msft · Accepted solution]

Hi , @genehsu 

According to your descrioption, the incremental refresh dose not work in you Power BI Service.

First , To be clear, Incremental refresh will not take effect on Power BI Desktop, it will only show the data you filtered in Power Quwey Editor, and will only work after you have configured the Incremental refresh policy correctly and published to the Power BI Service.

The first refresh on Power BI Service is a sequential full refresh, which creates the corresponding partition, and refreshes the historical partition and the incremental partition, and then the subsequent refresh will only refresh the incremental partition.

For more information, you can refer to :
Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn

Second, if our incremental refresh does not take effect on Power Bi Service, we need to check if there are steps in Power BI Query that prevent the query from collapsing, causing the incremental refresh to fail.

At the same time, you also need whether your configuration process is the same as described on the official website, and the parameters of the variables must be consistent here, because its underlying logic is to override the values of these parameters on the Power Bi Service, and then pass them to our underlying data source to obtain the corresponding data.

The steps you can refer to :
Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn

And you can also use the SSMS to connect the dataset to check the incremental refresh partion.SSMS can be used to view and manage partitions created by the application of incremental refresh policies. You can check the incremental refresh can work?

For more information, you can refer to:
Advanced incremental refresh and real-time data with the XMLA endpoint in Power BI - Power BI | Micr...

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Tutu_in_YYC]

Did you set up these settings correctly?

[lishapruthi]

Hi @Tutu_in_YYC ,

I have one issue, I am not getting the option of Archieve data starting when I am setting up the incremental refresh in Report view. Can you please help me to resolve this issue?

Also, one question, Can we able to see the historic data as well in visuals which we don't refresh while setting up the parameters? Example: I am setting the parameters start range and end range to 2 january 2022 - 31 January 2022 and filter the table as well in Power query but I have data of 2019-2022. So can i able to visualize my oldest data as well in Report view?

Thanks

[Tutu_in_YYC]

Hi @lishapruthi 
Did you get this?

[lishapruthi]

Hi @Tutu_in_YYC ,

No, now I am getting the below option,

But still the incremental refresh is not working for me.

[Tutu_in_YYC]

Based on the warning in the yellow banner, there is a possibility that it will not work with your query. 

Check the compatible data source and query folding in this documentation:
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#supported-data-...