A data engineer is maintaining a large semantic model in Microsoft Fabric that is experiencing slow query performance for complex measures. Many measures involve time-intelligence calculations (e.g., Year-to-Date, Previous Year Sales) and currency conversions. The model currently uses individual DAX measures for each calculation. The engineer wants to simplify the model, improve maintainability, and potentially boost performance by reducing the number of explicit measures. Which feature should the engineer implement?
- AAutomatic Aggregations
- BObject-Level Security (OLS)
- CIncremental Refresh
- DCalculation Groups
Show answer & explanationAnswer & explanation
Correct answer: D. Calculation Groups
Calculation Groups allow you to define a single set of calculation items (like 'Year-to-Date' or 'Previous Year') that can be applied to any existing measure. This significantly reduces the number of explicit measures, simplifies model maintenance, and can improve performance by optimizing query plans for shared calculation logic. This is ideal for managing time-intelligence and currency conversion variations across multiple base measures.
Why the other options are wrong
- A. Automatic Aggregations improve query performance by pre-calculating and caching summarized data, but they don't simplify the definition of complex measures like time intelligence.
- B. Object-Level Security restricts access to tables or columns and is unrelated to measure complexity or performance optimization.
- C. Incremental Refresh manages data refresh for large tables efficiently, but it does not address the complexity or performance of DAX measures.
Calculation Groups
Calculation Groups in Microsoft Fabric semantic models allow you to define a set of 'calculation items' that can modify the context of existing measures, reducing measure sprawl and improving model maintainability.
- Consolidate common measure logic (e.g., time intelligence).
- Apply to multiple base measures dynamically.
- Simplify model development and maintenance.
Memory trick: Aggregates Speed Up, Groups Simplify, Refresh Stays Fresh.