A data modeler is optimizing a semantic model in Microsoft Fabric. The model contains a 'Sales' table with millions of rows and a 'Date' table. A frequently used measure is `Total Sales YTD = CALCULATE(SUM(Sales[SalesAmount]), DATESYTD('Date'[Date]))`. Users report slow performance when using this measure, especially for historical years. The underlying 'Sales' table is in Import mode. Which optimization technique should the data modeler prioritize to improve the performance of this specific measure?
- AAdd a calculated column for 'Year' to the 'Sales' table and filter on it.
- BChange the storage mode of the 'Sales' table to DirectQuery.
- CImplement automatic aggregations on the 'Sales' table.
- DOptimize the DAX expression by using `FILTER` with `ALL`.
Show answer & explanationAnswer & explanation
Correct answer: C. Implement automatic aggregations on the 'Sales' table.
Given the 'Sales' table has millions of rows and is in Import mode, and the measure involves common aggregations (SUM) over time intelligence, implementing Automatic Aggregations is the most impactful solution. This would pre-calculate and store the aggregated sales data, drastically speeding up queries for YTD and similar time intelligence measures, while still allowing drill-through to detail.
Why the other options are wrong
- A. Adding a 'Year' column might simplify some filters but won't fundamentally speed up the aggregation of millions of rows over a time range.
- B. Changing to DirectQuery would provide real-time data but would likely worsen performance for complex aggregations on millions of rows, as it pushes queries to the source.
- D. While DAX optimization is generally good, the `DATESYTD` function is already optimized for time intelligence. The core issue is aggregating millions of rows, which is best addressed by pre-aggregation, not just DAX syntax changes.
Measure Performance Optimization (Aggregations)
For semantic models with large fact tables in Import mode, optimizing measures that perform common aggregations (especially time intelligence) is often achieved by implementing aggregations (manual or automatic) to pre-calculate results.
- Aggregations significantly reduce the number of rows processed at query time.
- Automatic Aggregations are managed by Fabric, simplifying implementation.
- Especially effective for measures involving SUM, COUNT, AVG over large datasets.
Memory trick: Millions of rows, time intelligence slow, aggregates make the data flow.