Forum Discussion
Partitioning based on Year-Month
Hi NEJO ,
Thanks for reaching out to the Microsoft fabric community forum.
In Power BI and Fabric, partition pruning is most effective when filters are applied directly to the column used for partitioning in the fact table. When filters are applied indirectly such as through a related dimension table partition pruning typically does not occur. This is because the query engine cannot reliably determine which partitions to scan until after joins are evaluated, which happens too late in the query execution process for pruning to take effect. To improve pruning in such scenarios, it’s recommended to include commonly filtered attributes (like FiscalYear) directly in the fact table, allowing filters to act on those columns directly. This denormalization ensures that partition pruning can occur and helps optimize query performance.
If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you
Thanks for the response.
How would I achieve that though, as my model allows for selection of anything from the caelendar (day, month, year, calendar year, fiscal year etc) from my dimension, so the join and filtering will never be direct on period from within the fact table directly, as the join will always be on date. I have multiple fact tables that are required to filter based on that selection from the dim table, so it doesn't seem right to apply the filter to the fact table(s) directly.
Maybe partitions are not required in my instance, given the size of my data tables - but would be good to understand how to get around this type of scenario for future developments.
Thanks
Neil
Hi NEJO
Unfortunately, we have to work around this limitation, as partition pruning is only triggered when the filter condition is applied directly to the partitioned column in the fact table, rather than through a relationship or join via a dimension table.
Given your current data volume (40 million rows per fact table) and a well-structured star schema, it's very possible that partitioning may not yield a noticeable performance benefit especially in Direct Lake mode, where data access is optimized for read operations on delta tables. In fact, excessive partitioning can increase management overhead without a proportional benefit at this scale.
If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you