The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a column of Date/Time type.
I want to extract the time to use in Line Chart.
Method 1
I duplicated the column with Date/Time type, then changed Data type to Time under Column Tools -> Structure.
When i pull this field into Line Chart, it still shows the Month information, as if the type change was just a display change and the actual data still exists as date/time?
Method 2
I tried another way of creating time using Table tools ->New Column, then entering DAX
time = FORMAT(table[col],"hh:mm:ss").
Then i changed the Data Type from Text to Time, I got a pop up warning about "Your data will be stored differently" and accepted it.
Does this mean in the first method, the date never actually got truncated away?
Now the x-axis correct slyhows only the time with no date information.
What is going on?
How should i extract time, or date, if i want to visualize properly?
Also, I noticed the formatted data is getting rounded with the FORMAT method. My raw data from db contains milliseconds.
10/9/2023 10:25:02 am -> 10:25:02 (Type change) vs 10:25:03 (FORMAT)
13/9/2023 10:01:36 am -> 10:01:36 (Type change) vs 10:01:36 (FORMAT)
9/9/2023 11:40:06 am -> 11:40:06 (Type change) vs 11:40:07 (FORMAT)
Is this further evidence that method 1 of extracting time is simply a display change with less "intelligence"? (eg. rounding).
If method 1 causes pitfalls in visualization, should we ever use method 1?
I haven't tried using power query to do this. Why would someone do this time extraction in power query instead of DAX?
It feels like extra steps to "open powerquery, do steps, apply".
Solved! Go to Solution.
@gitgithan There is no date or time data type in DAX, only datetime. DAX Data types – DAX Guide
I ran an experiment. If you have a datetime field and switch it to time you see only the time. If you switch it back to datetime, still only the time. But if you switch it to date, then you see the original date, so the original date is still there from the looks of it.
@Greg_Deckler
Let me confirm that. You are saying the Data type dropdown does not change the data type?
In both methods i used this dropdown.
Method 1 changed Date/Time to Time
Method 2 changed Text to Time (With additional popup warning)
Method 2's warning makes me think method 1 is also actually changing data type and not just format. Also there's another dropdown for Format so i don't think Data type dropdown is for formatting.
Is there this nuance where, if i use the Data type dropdown, and move across options of {Date/time, Date, Time}, or {Whole number, Decimal number, Fixed decimal number}, it's a format change.
But if i mentally group the numbers and dates as 1 type each, and move across {The 3 numbers, The 3 dates, Text, True/False, Binary}, only then there is a type change?
Your method worked great, thanks for introducing.
Would you know why when I tried repeating method 2 on another similar column, but with NULLS, i get "Cannot convert value '' of type Text to type Date."
It's confusing because i'm not even selecting Date from the Data type dropdown, but Time. Your method works for this column too, and nulls are left alone as nulls after conversion.
Explanation of Greg's expression
Datetime values are stored as floating-point numbers (https://dax.guide/dt/datetime/), where the integer part represents the number of days since a specific epoch (usually December 30, 1899), and the fractional part represents the fraction of a day (time).
The integer part of the datetime value represents the whole days.
Subtracting the integer part from the original datetime value isolates the fractional part, which corresponds to the time of day.
@gitgithan There is no date or time data type in DAX, only datetime. DAX Data types – DAX Guide
I ran an experiment. If you have a datetime field and switch it to time you see only the time. If you switch it back to datetime, still only the time. But if you switch it to date, then you see the original date, so the original date is still there from the looks of it.
@gitgithan You're just changing the format, not the data type. If you just want the time you can do something like:
Column = [Column] - INT([Column])
Format it as Time and should be good to go.