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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
tobiasnygren
Helper IV
Helper IV

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors