Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Easy
A data engineer is developing a semantic model in Microsoft Fabric. The model sources data from a data warehouse where a 'Date' table exists, but it is not explicitly marked as a date table in the semantic model. This omission is causing issues with DAX time intelligence functions, as they are not producing correct results for year-to-date and month-to-date calculations. What is the most direct action the engineer should take to resolve this issue?
- AChange the data type of the 'Date' column to Text.
- BImplement a many-to-many relationship with the 'Date' table.
- CMark the 'Date' table as a date table in the semantic model.
- DManually create new calculated columns for YTD and MTD.
Show answer & explanationAnswer & explanation
Correct answer: C. Mark the 'Date' table as a date table in the semantic model.
For DAX time intelligence functions to work correctly, the semantic model needs a designated date table. Marking the 'Date' table as a date table explicitly tells the DAX engine which table to use for time intelligence calculations, providing the necessary context.
Why the other options are wrong
- A. Changing the 'Date' column to Text would break all date-based functionality, including time intelligence.
- B. A many-to-many relationship is generally not appropriate for a standard date table, which typically has a one-to-many relationship with fact tables.
- D. Manually creating calculated columns for YTD/MTD is inefficient and bypasses the power of built-in time intelligence functions.
Mark as Date Table
Marking a table as a date table in a Microsoft Fabric semantic model designates it as the primary source for date-related filtering and enables correct functionality of DAX time intelligence functions.
- Essential for DAX time intelligence functions.
- Requires a single, continuous date column.
- Provides context for YTD, MTD, QTD calculations.
- Ensures proper date hierarchies and filtering.
Memory trick: Date Table: Mark It, Link It, Then Calculate!