Dealing with milliseconds in timestamps

[p1ngu1n]

Hi all,

I have data which has timestamp vaules with milliseconds in it. In most cases the milliseconds are 0, means the timestamps ends on ".000". In rare cases they do not and this is where the problem occurs.

Looking at the data in the query editor it looks like Power BI does not parse this data right. I have a few measures which reference to these timestamps and they do not work for all the items with milliseconds != 0.

Any idea on how to solve and keep the DirectQuery option to my AzureSQL DB?

Thx!

[v-yuta-msft · Accepted solution]

@p1ngu1n ,

If the millionseconds time does not show properly in visual. I would suggest you convert the time into precise millionseconds and then concatenate hour, minute, second and millionsecond in TEXT format. 

For more details about this workaround, you can refer to blog below:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Milliseconds-Duration/td-p/406698

In addtion, the actual time is still correct if you use them in your measure/calculate column, you can click table view and check the original data.

Regards,

Jimmy Tao

[Jeremy_R]

It's fairly unbelievable that in 2024 I'm still dealing with the leading upper right quadrant Gartner BI and Analytics tool which cannot seem to deal with fractional seconds in datetime data properly.  This placement is becoming more and more puzzling to me.  I'm finding that filtering on datetime values doesn't work properly when fractional seconds are involved (just blanks out the whole visual), visual displays don't work to that precision, etc.  Having to make datetime values into Text and whatnot as workarounds is something we should've stopped doing 15-20 years ago. 

Power BI has very poor support it seems for things like DirectQuery data as well, and when you do leverage it, it then almost always fails to support any sort of useful data relationships in the model nor push down query criteria in an acceptable way.  These issues do not seem to get fixed rather wind up added to various lengthy documentation as something that "isn't supported" which you eventually find later after struggling for days with a particular issue.