Create partitions along with the incremental partitions

[dmkblesser]

Is it possible to create a data partition along with the paritions created by the incremental refresh?

Here is what I've tried: I set up a model with incremental refresh with policies set to be archive 5 year data from now and refresh the most 30-day data. Publish the model to the service and let it refresh to create all the partitions. 

Then I connect to the model through XMLA, on top of all the partitions, I tried to create another partition with DirectQuery connection and target for all the data before 5 years from now and hope this partition can cover the cold data. However, when I tried to refresh the model from the Power BI service, the manually created partition got removed. 

Has anyone implemented anything similar and be able to retain the cold partition and let the incremental refresh take care of the future partition creation? Could you share what you have done to make it work? Appreciate the help!

[Poojara_D12 · Accepted solution]

Hi @dmkblesser 

What you’re attempting—extending a Power BI semantic model with a custom partition alongside the partitions created by incremental refresh (IR)—is an advanced and often-requested use case, but unfortunately, it’s not supported in a stable or persistent way in the current Power BI architecture. When you define and publish a model with incremental refresh policies, Power BI takes full control of partition management. This includes creating, merging, and deleting partitions based on the policy definitions (e.g., 5-year archive + 30-day refresh window). When a scheduled refresh or even a manual refresh is triggered in the service, the Power BI engine re-evaluates and resets all partitions to match the defined policy, which is why your manually added “cold” DirectQuery partition (via XMLA) gets deleted—it’s not recognized as part of the IR policy and is treated as an inconsistency.

There is no supported way today to mix manually created partitions with IR-managed partitions in the same table in Power BI. Any changes made to the partitioning scheme outside of the IR engine are considered transient and will be overridden. This limits hybrid strategies like separating long-term historical data (cold, possibly in DirectQuery) and more recent data (hot, via Import) within a single table.

A possible workaround is to split the data into multiple fact tables—for example, one for historical cold data using DirectQuery or a rarely refreshed import table, and another with IR for recent data. You can then union the two tables using a composite model or create a semantic layer over both using calculated tables or views in your source data layer. This way, IR manages only the hot portion while your cold partition remains untouched in a separate logical structure.

Until Microsoft provides native support for mixed-mode partitioning or multi-range IR, which is on many users’ wishlists, carefully structuring your data model across multiple tables is the most reliable way to achieve your goal without having your custom partition removed after each refresh.

[dmkblesser]

Hi @Poojara_D12 , thank you for your response. I liked it that you be straight to the point that it is not supported at this point. I often get confused by different resources with a tempting solution but when I tried, it did not work. 

Also liked your workaround by set up a semantic model with the composite mode. I think I might just go down with that route. thank you again

[v-sathmakuri]

Hi @dmkblesser ,

May I ask if the provided solution helped in resolving the issue. Please let us know if you have any further issues.

Thank you !! 

[v-sathmakuri]

Hi @dmkblesser ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Deku  and @Akash_Varuna  for the prompt response.

1. Enable Incremental Refresh in Power BI Desktop (e.g., archive 5 years, refresh last 30 days).
2. Publish and run a full refresh to create the partitions.
3. Open Tabular Editor (v3) and connect via XMLA.
   Set IsHybrid = true on the table.
4. Add a new partition for cold data with:
   Mode = DirectQuery
   A filter like: WHERE Date < DATEADD(YEAR, -5, GETDATE())
5. Add DataCoverageDefinition to the Dire Query partition to define its date range. This avoids performance issues.

Thanks & Regards,

Rekha Athmakuri.

[dmkblesser]

Hi @v-sathmakuri , this is the message I received when I tried to add a new partition to the same table 

[Deku]

Please see this video on how to setup reverse hybrid tables

https://youtu.be/Hpx52kOodPc?si=1Bg6aLlAv5gKXcjW

[dmkblesser]

Hi Deku, thanks for your response. This is the concepet I tried to implement and I actually started with that video. Just curious to know if you personally made it work by following the video instructed, cuz it didn't work out for me. If it does work for you, any modification you made from the instruction? Also, since in the video, Pat didn't use the DataCoverageDefinition to tell the engine which is truly the DQ parititon, I'm wondering if you have experienced any slow performance for the Import partitions when you implemented it. 

[Deku]

I've not really used them. In cases where I have wanted to use hybrid tables, I've wanted to use user defined aggregation more, and they are incompatible 

[Akash_Varuna]

Hi @dmkblesser For this set up incremental refresh for recent and archive data. After publishing, use the XMLA endpoint to manually create a cold data partition targeting older data. Ensure the incremental refresh policy excludes periods covered by the cold partition. Alternatively, pre-aggregate cold data in the source and include it as a separate table.

[dmkblesser]

Hi @Akash_Varuna, can you show me a sample table scrip or a screenshot of the setup? 
I tried with Tabular Editor and this was the error message I received: 

the error message sounds like mix custom partition and incremental partitions is not allowed.