Applying IncreApplyimental Refresh with Detect Data Changes for Dimension Tables in Power BI Premium

[SniperPro86]

I’m working with a Power BI Premium dataset where I’ve successfully configured incremental refresh with the Detect Data Changes feature for fact tables. However, I’m facing a challenge with dimension tables.

I don’t want dimension tables to refresh every time I trigger a dataset refresh. Instead, I want to configure Detect Data Changes for dimension tables so they only refresh when the source data changes.

Is there a way to apply incremental refresh with Detect Data Changes specifically for dimension tables without refreshing them unnecessarily during a dataset refresh?

I tried to create a dummy Date on the dimension where I put the first day of the current year for all the rows, but this is not working because process will create duplicates on the partitions for the next year.

[rajendraongole1]

Hi @SniperPro86 - In Power BI, incremental refresh with the Detect Data Changes feature is primarily designed for fact tables, but it can also be applied to dimension tables with some additional considerations. Dimension tables typically do not have a natural partitioning column like fact tables, which makes their incremental refresh setup more nuanced. 

Configuring incremental refresh with Detect Data Changes for dimension tables requires a robust mechanism to track changes, such as a "Last Modified Date" column. Avoid using dummy dates and ensure unique identification of rows to prevent duplicates. If your dimension tables change infrequently, consider separating their refresh process from fact tables to optimize performance.

Links:

Chris Webb's BI Blog: Custom Queries For “Detect Data Changes” In Power BI Incremental Refresh

Customizing Detect Data Changes in PowerBI Incremental Refresh - Ross Couldrey - Data Blogs

[SniperPro86]

Hello @rajendraongole1 
Thank you! I want to create only one pipeline which refrech the dimensions in case there is an update and the facts tables it's more practical in my case.
I already have an UpdateDate column in my dimensions (I used that column for detect data change), I aded an 'incremental_date' column where I put '2024-01-01' and that's whats I used in source expression to create partitions. I created the refresh policy with 1 year rolling and 1 year incremental.
This would work fine for 2024 with all the data in 2024 partitions but in 2025 I wanted to change the value of incremental_date to '2025-01-01' but I will have data twice in 2024 partition and 2025 partition.