PL-300 · Question #79
You plan to join a fact table named ActivityLog to a Date dimension named ActivityDate. The date value in ActivityLog is a datetime column named ActivityStart. The date value in ActivityDate is a…
The correct answer is D. Create a calculated column in ActivityLog that uses the FORMAT DAX function. Before creating a relationship between a datetime column and a YYYYMMDD integer column, create a calculated column in the fact table to convert the datetime to a matching YYYYMMDD integer format.
Question
You plan to join a fact table named ActivityLog to a Date dimension named ActivityDate. The date value in ActivityLog is a datetime column named ActivityStart. The date value in ActivityDate is a number column named DateID. DateID is in the YYYYMMDD format. What should you do in the model before you create the relationship?
Options
- AChange the Data Type of ActivityStart to Date.
- BCreate a measure in ActivityLog that uses the FORMAT DAX function.
- CChange the Data Type of DateID to Date.
- DCreate a calculated column in ActivityLog that uses the FORMAT DAX function.
How the community answered
(21 responses)- A5% (1)
- C10% (2)
- D86% (18)
Why each option
Before creating a relationship between a datetime column and a YYYYMMDD integer column, create a calculated column in the fact table to convert the datetime to a matching YYYYMMDD integer format.
Changing the Data Type of `ActivityStart` to Date would still leave `ActivityDate[DateID]` as a YYYYMMDD number, preventing a direct match for a relationship without further transformation.
Creating a measure using the `FORMAT` DAX function is for display purposes in visuals and does not create a persistent column that can be used to establish a relationship.
While changing the Data Type of `DateID` to Date is a valid transformation, it's generally better practice to adapt the fact table to the dimension, and this option would involve modifying the dimension table, which might not always be desired.
To create a relationship, the join columns must have matching data types and formats. Creating a calculated column in the `ActivityLog` table that derives a YYYYMMDD integer from `ActivityStart` (e.g., using `YEAR([ActivityStart])*10000 + MONTH([ActivityStart])*100 + DAY([ActivityStart])` or `VALUE(FORMAT([ActivityStart],"yyyymmdd"))`) allows for a direct integer-to-integer join with `ActivityDate[DateID]`, ensuring the relationship can be established correctly for efficient data modeling.
Concept tested: Data type and format alignment for relationships, calculated columns
Source: https://learn.microsoft.com/en-us/dax/calculated-columns
Topics
Community Discussion
No community discussion yet for this question.