A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Sales' fact table and a 'Date' dimension table. The 'Date' table contains columns like 'DateKey', 'FullDateAlternateKey', 'DayNumberOfWeek', 'MonthName', 'CalendarYear'. The modeler needs to ensure that all time intelligence functions (e.g., YTD, MTD, QTD) work correctly and efficiently across the model. What is the most critical property to configure for the 'Date' table to enable proper time intelligence?
- ACreate a one-to-many relationship from 'Date' to 'Sales' on the date key columns.
- BMark the 'Date' table as a date table in the model.
- CEnsure the 'DateKey' column is set as the primary key.
- DSet the 'Data Category' property for 'FullDateAlternateKey' to 'Date'.
Show answer & explanationAnswer & explanation
Correct answer: B. Mark the 'Date' table as a date table in the model.
To enable time intelligence functions (like TOTALYTD, TOTALMTD, etc.) in DAX to work correctly, you must explicitly mark the 'Date' table as a date table in the semantic model. This tells the DAX engine which table and column to use for its internal time intelligence calculations. While other steps are important for a good date table, marking it as a date table is crucial for time intelligence functions.
Why the other options are wrong
- A. Creating the relationship is necessary for filtering, but marking the table as a date table is what allows the specific DAX time intelligence functions to operate correctly over that table.
- C. While setting a primary key is good practice for relationships and data integrity, it's not directly what enables time intelligence functions within DAX.
- D. Setting the 'Data Category' to 'Date' for a column is good practice but does not, by itself, enable the model's time intelligence capabilities.
Mark as Date Table
Marking a table as a 'Date Table' in a Microsoft Fabric semantic model is a critical step to enable and correctly utilize DAX time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR). It tells the DAX engine which table contains the definitive date column for time-based calculations.
- Essential for DAX time intelligence functions to work.
- Requires a single, continuous date column with unique values.
- Configured in the model view by right-clicking the table.
- Ensures correct context for time-based calculations.
Memory trick: To make time intelligence smart, mark the date table's heart.