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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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