Relationship to time not working for one of my tables

[Anonymous]

Why do I not get Hour from my time dimension in the model in the left visual.
Example file
https://www.dropbox.com/s/tq6yepq7j7w6m51/Test.pbix?dl=0

[HotChilli]

Ok, I changed the datatype to text (in the Fact table, in Data view) and then changed it back again to time - and the relationship worked.  So something was going on behind-the-scenes.

[HotChilli]

The relationship is set up correctly but there are no matches between the 2 tables.

i.e. there is no 3.55.23 in the time dimension

edit: hang on, i've just sorted the table, it's in there

[Walter_W2022]

@HotChilli , yeah, you are correct, the data types are all same as time, very strange, 3:55:23 AM does exist in table MyTimDim 😀

[Walter_W2022]

Hi,

I could not check the data type for column Time in Fact table, the columns have to be the same data type to create relationship, which should be "Time" as well 

[Anonymous]

The file with dummy data
https://www.dropbox.com/s/3ti66v6uwv4tawy/TEST.xlsx?dl=0

[Anonymous]

[Walter_W2022]

Hi @Anonymous , your data in time column in Fact table is  is incorrect, in power query editor, it is 03:55:24, actually should be 3:55:24 AM, I have created a table named "FactTest", which has relationship with MyTimDim, everything works fine, you have to double check 😀

20230330-Test.pbix

[Anonymous]

Looks like you have done the same test I did in "FactTime" that is working.
I still do not understand why the data is incorrect in the file. What do I need todo to correct that?
The data is exported from Microsofts Capacity Metric app - Backgroundtasks.

[Walter_W2022]

@Anonymous , sorry, I did import the test.xlsx into test.pbix, which has the exact same issue with yours, now I have the same question. But let me try again😀, will keep you updated. 

[Walter_W2022]

@Anonymous I think I found the root cause, it is because the milliseconds, now need to found a way to remove the millisecond😀

[Walter_W2022 · Accepted solution]

@Anonymous it is working now, we cannot use DateTime.Time([Start]) to extract time, as it will contain milliseconds, you have to use 

Time.From(Time.ToText(DateTime.Time([Start]), "hh:mm:ss"))

To extract time, which does not have milliseconds, hope this helps.

[Anonymous]

Thanks alot for your help, it is working now. 😀
So when splitting datetime column to a time column
you should not use "Add new column -> Time only"😅 if you do not want to include milliseconds.

[Walter_W2022]

no problem, my pleasure😀