Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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😀
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
104 | |
99 | |
97 | |
41 | |
38 |
User | Count |
---|---|
151 | |
123 | |
79 | |
73 | |
71 |