Aggregation table is ignored after creating partitions

[rfgonc]

I have a model where the fact table is in Direct Query with a huge amount of data. Added an aggregation table in import mode and loaded some data. I've configured the aggregations in PowerBI desktop, published to the service and everything works as expected.

Because the volume in the aggregation table is high, I've created some partitions in the aggregation table in Tabular editor. After creating the partitions, the measures that would hit the aggregation table previously, now go to DQ.

I've tested this in DAX Studio... ran a query before creating the partitions: Aggregation table is hit 

Create a partition, run the same query again: Query goes to DQ.

I've also tried to create the partitions before configuring the aggregations (in this case the configuration was done in tabular editor) and didn't work.

Anyone knows how to solve this? Any workaround?

Thanks in advance

[rfgonc · Accepted solution]

After some tests we realized that if an aggregation table has any unprocessed partitions, the table is ignored.
We partitioned the table by year_month and created partitions for future dates and remained unprocessed and was causing the issue.

[rfgonc]

Hi @Anonymous ,

Thanks for the reply, but after creating the partitions I won't be able to open the model in Power BI desktop.

Regarding the partition compatibility, I even tried duplicating the existing partition in Tabular Editor, and as soon I save the changes and run the same query, the query goes to DQ instead of hitting the aggregation.

I tried to replicate the behaviour in a different model in a different tenant and the same is happening. Once I create the partition, the aggregation isn't hit anymore.

[Anonymous]

Hi @rfgonc 

Here are a few steps to help troubleshoot and resolve this issue:

1. Review Aggregation Configurations: Ensure that after partitioning, the aggregation configurations are still correctly set up. It's crucial that the aggregation table and its partitions are properly recognized by Power BI. Revisit the "Manage aggregations" dialog in Power BI Desktop to confirm that the configurations are intact and correctly applied. For more details on managing aggregations, please refer to the User-defined aggregations - Power BI | Microsoft Learn
   
   

2. Check Partition Compatibility: Ensure that the partitions created in the aggregation table are compatible with the aggregation configurations. The partitions should not alter the granularity or the structure expected by the aggregation setup. It's also important to ensure that the data types between the detail and aggregation columns match.

Best Regards,

Jayleny