Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Medium
A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Date' table which is marked as a date table. The modeler defines several time-intelligence measures, such as 'Year-to-Date Sales' and 'Previous Year Sales'. When users query these measures, they consistently report incorrect values. Upon investigation, the modeler finds that the 'Date' table does not have a continuous date range, with some missing dates. What is the most likely cause of the incorrect time-intelligence calculations?
- AThe relationship between the 'Date' table and the fact table is inactive.
- BThe data types of the date columns in the 'Date' table are not set to 'Date' or 'DateTime'.
- CThe 'Date' table is marked as a date table, which is causing conflicts with the time-intelligence functions.
- DThe time-intelligence functions require a complete and continuous date table to function correctly.
Show answer & explanationAnswer & explanation
Correct answer: D. The time-intelligence functions require a complete and continuous date table to function correctly.
DAX time-intelligence functions rely on a complete and continuous date table to accurately calculate periods like Year-to-Date or Previous Year. Missing dates break the continuity, leading to incorrect results.
Why the other options are wrong
- A. An inactive relationship would prevent the 'Date' table from filtering the fact table at all, not just cause incorrect time-intelligence results.
- B. Incorrect data types would likely cause errors during model processing or prevent relationships from being established, rather than leading to subtly incorrect calculations after relationships are set.
- C. Marking a table as a date table is a prerequisite for time-intelligence functions, not a cause of conflict.
Continuous Date Table
A prerequisite for DAX time-intelligence functions, requiring a date dimension table that contains every single date within its defined range, with no gaps.
- Essential for accurate time-intelligence calculations.
- Must have a unique date column with a date or datetime data type.
- Typically generated using M query or DAX (CALENDARAUTO/CALENDAR).
Memory trick: Date table must be complete, marked, and linked.