Forum Discussion
Data Modeling Validation: Should Semi-Additive Measures Always Be in a Separate Fact Table?
Thankyou, TomMartens, for your response.
Hi Mahhin_Shahzad1,We appreciate your inquiry submitted to the Microsoft Fabric Community Forum.
Based on my understanding, the issue arises because semi-additive or balance sheet measures such as Assets, Liabilities, and Inventory behave differently from fully additive or P&L measures such as Sales and Expenses. While P&L values can be summed across time periods, balance sheet values are point-in-time measures and therefore require functions like LASTDATE() or LASTNONBLANK() for correct aggregation. Combining both types of measures in a single fact table increases aggregation complexity, complicates DAX logic maintenance, and may lead to performance overhead.
As a best practice, it is advisable to keep semi-additive measures (balances) in a separate fact table (for example: FactMonthlyBalances) and fully additive measures in another fact table (for example: FactMonthlyFlows). Both tables can share the same Date, Company, and Measure dimensions to facilitate cross analysis. This design ensures accurate time aggregation, simplifies DAX, and enhances scalability.
Key recommendations:
1.Keep semi-additive measures in a separate fact table. This simplifies DAX and prevents incorrect aggregations.
2.Use the periodic snapshot pattern for forecasting. This approach is appropriate for on demand forecasting and monthly balance reporting.3.Separate tables for performance and scalability. A single consolidated table containing mixed measure types can increase query complexity and impact performance. Separate tables allow partitioning and better model optimisation.
Additionally, please refer to the links below for further information:
Modeling Fact Tables in Warehouse - Microsoft Fabric | Microsoft Learn
Define Semiadditive Behavior | Microsoft Learn
Optimization guide for Power BI - Power BI | Microsoft LearnWe hope the information provided helps to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.
Thank you.
Hey Mahhin_Shahzad1 ,
according to Kimball's "The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling" there are different types of fact tables, e.g., transaction-based fact tables and snapshot tables.
I consider P&L data as transactional data, meaning two rows can be aggregated over time, e, g., revenues can be summed. In contrast most often balance data must be considered as snapshot data, meaning March data can not be summed with April data, instead it's something like LASTDATE ...
Assuming there is only balance data available until April, then 1. Halfyear is April, 2. Quarter is April, but 1. Quarter is March, and not the sum of Jan - March.
For this reason, I separate "Semi-Additive" measures from the transactional data. I separate the Balance data from the P&L data.
Not sure, if my approach can be considered a best-practice, but until now I did not face issues regarding scalability.
Hopefully, this helps somehow.
Regards,
Tom