Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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.
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.
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 😀
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
The file with dummy data
https://www.dropbox.com/s/3ti66v6uwv4tawy/TEST.xlsx?dl=0
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 😀
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😀
@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.
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😀
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
71 | |
49 |
User | Count |
---|---|
141 | |
121 | |
112 | |
60 | |
58 |