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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
tobiasnygren
Resolver I
Resolver I

Relationship to time not working for one of my tables

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

tobiasnygren_0-1680171997811.png

tobiasnygren_1-1680172040712.png

 

1 ACCEPTED SOLUTION

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

 

 

wangbt89_2-1680178784780.png

 

 

wangbt89_3-1680178918092.png

 

 

View solution in original post

13 REPLIES 13
HotChilli
Super User
Super User

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
Super User
Super User

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

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

 

wangbt89_0-1680174652824.png

 

Walter_W2022
Resolver II
Resolver II

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 

 

wangbt89_0-1680174043729.png

wangbt89_1-1680174055271.png

wangbt89_2-1680174064050.png

 

 

tobiasnygren_0-1680175341674.png

 

Hi @tobiasnygren , 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

 

wangbt89_0-1680175888686.png

wangbt89_1-1680176027739.png

 

wangbt89_2-1680176051432.png

 

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.


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

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

 

wangbt89_0-1680178472790.pngwangbt89_1-1680178512368.png

 

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

 

 

wangbt89_2-1680178784780.png

 

 

wangbt89_3-1680178918092.png

 

 

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.

no problem, my pleasure😀

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.