Incremental Refresh - Detect Data Change Issues

[wainnoce]

Problem: Detect data change toggle does not seem to be working for my specified dataset.

Configuration:

- To keep it simplistic I have a table with 3 columns. ID, CreatedDate, LastModifiedDate.

- I have set parameters on the created date for range start and range end.

- Setup archive to last 12 months of data creating 12 partitions.

- Setup incremental refresh for the last 2 days.

- Enabled detect data changes for the last modified date.

- Published to Power BI premium capacity workspace. 

- Initialized connections to gateway and performed a refresh to trigger the incremental refresh.

Steps I have done so far to trouble shoot:

- After doing what was aformentioned I then went into the development database the dataset is hooked up to and updated and inserted the following five records. Assume the columns are in the following order (ID, CreatedDate, LastModifiedDate).

Updated:

123, 07-01-23, 01-01-24 (Did not come through) - Does not make sense. Last modified was originally 11-22-23.

456, 01-01-24, 01-01-24 (Did come through) - Does make sense. This actually caused a duplicate record since I changed both date fields.

Inserted:

789, 01-01-24, NULL (Did not come through) - Doesn't make sense. If insert a record within last 2 day frame it should refresh. Even if last modified is null.

101, 12-31-23, NULL (Did not come through) - Makes sense. Outside of 2 day refresh window.

303, 01-01-24, 01-01-24 (Did come through) - Makes sense. New record inside two day refresh window.

Can any one explain what could be happening or what I have setup wrong during configuration?

[v-xuxinyi-msft]

Hi @wainnoce

Here is my test of incremental refresh by connecting to a SQL server data source.

After my testing, if you set parameters on the LastModifiedDate Column, the following happens:

Here is the test procedure that I used to get the result:

1. Create the original data table.

2. Connect to SQL server on Power Bi and get the table. And set parameters on the CreatedDate column.

3. Configuration for incremental refresh

After the configuration on the desktop was completed, I published the report to the service.

4. After the first refresh

5. Updates were made and new data inserted

6. The result

Is this the result you expect?

About more information of configuring incremental refresh: 

Configure incremental refresh and real-time data for Power BI semantic models - Power BI | Microsoft...

Best Regards,
Community Support Team _Yuliax

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

[wainnoce]

No. Given your example I need to turn on the Detect Data Changes button and have that set to the Last Modified Date column. That way ID 123 would have been updated to reflect the Last Modified Data column being updated from 1/1/24 -> 1/9/24. This is the crux of the issue I am running into. 

[3CloudThomas]

Create a support ticket with Microsoft to get to root issue. There have been issues with Detech Data Changes in the past.