A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Sales' table with 'OrderDate' and a 'Returns' table with 'ReturnDate'. The model also has a 'Date' dimension table. The requirement is to enable efficient time intelligence calculations (e.g., Year-to-Date sales, Month-over-Month returns) across both sales and returns data using the single 'Date' dimension. How should the 'Date' dimension table be configured to support this?
- AMark the 'Date' table as a date table, and then create a calculated column in 'Sales' and 'Returns' for year, month, and day.
- BEstablish an active relationship between 'Date[Date]' and 'Sales[OrderDate]', and an active relationship between 'Date[Date]' and 'Returns[ReturnDate]'.
- CCreate two separate 'Date' dimension tables, one for 'Sales' and one for 'Returns'.
- DEstablish an active relationship between 'Date[Date]' and 'Sales[OrderDate]', and an inactive relationship between 'Date[Date]' and 'Returns[ReturnDate]' to be activated by DAX.
Show answer & explanationAnswer & explanation
Correct answer: D. Establish an active relationship between 'Date[Date]' and 'Sales[OrderDate]', and an inactive relationship between 'Date[Date]' and 'Returns[ReturnDate]' to be activated by DAX.
To use a single 'Date' dimension for multiple date columns in different fact tables, one relationship should be active (e.g., for 'Sales') and the others inactive. The inactive relationships can then be activated as needed using DAX functions like `USERELATIONSHIP()` for specific time intelligence calculations (e.g., for returns).
Why the other options are wrong
- A. While marking the 'Date' table is good practice, creating calculated columns for year, month, day in fact tables is inefficient and doesn't solve the problem of having a single 'Date' dimension filter multiple date columns effectively without inactive relationships.
- B. Two active relationships from the same 'Date' table to different columns in the model will create ambiguity and typically result in an error or unexpected behavior, as only one path can be active for filtering.
- C. Creating separate date tables duplicates data and complicates time intelligence calculations across both fact tables.
Inactive Relationships
Inactive relationships in a semantic model allow a single dimension table to relate to multiple date columns in fact tables, activated as needed by DAX functions.
- Used when a dimension relates to multiple columns in one or more tables.
- Activated using `USERELATIONSHIP()` in DAX.
- Prevents ambiguity in filter propagation.
Memory trick: Dates flow, one active, others wait, DAX then chooses to activate.