Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
gitgithan
Frequent Visitor

Why does Line Chart still show datetime when I already extracted time by changing Data type

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?

gitgithan_1-1717688324926.png

 


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. 

gitgithan_0-1717688295046.png

 



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".

 

1 ACCEPTED 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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
gitgithan
Frequent Visitor

gitgithan_0-1717691471413.png

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Community Champion
Community Champion

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.