Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
I am trying to create a graph using time/date data to show the sum by hour of first responses to tickets. To do this, I removed the date from the time column and rounded to the nearest half hour, accounting for null values, using the following expression:
First Response Time = IF(ISBLANK(Metrics[SLA First Response Date/Time]), MROUND("00:00:00", "00:30:00"), MROUND(FORMAT(Metrics[SLA First Response Date/Time], "hh:mm:ss"), "00:30:00"))
This returns a column that only displays time values (even if the data type is set to date/time), but when the data type is set to Time and a graph is made, the X axis formats the time as if it had a date:
My first assumption is that DAX sets the datatype to Time/Date by default but I am unsure how to change it such that only the time values are displayed on the graph. Any suggestions on this would be appriciated
Solved! Go to Solution.
After doing some digging, I was able to determine the cause of this issue. The problem comes from the MROUND function, which when rounding the time would round both up and down to 12:00:00 AM. When rounding up, the time is technically being rounded to 24:00:00, or 12:00:00 AM on 12/31/1899. To solve this issue, I only rounded in the downward direction to the nearest half hour:
First Response Time =
VAR Hour = HOUR(Metrics[SLA First Response Date/Time])
VAR Minute = MINUTE(Metrics[SLA First Response Date/Time])
RETURN IF(Minute<30,TIME(Hour,0,0),TIME(Hour,30,0))
After doing some digging, I was able to determine the cause of this issue. The problem comes from the MROUND function, which when rounding the time would round both up and down to 12:00:00 AM. When rounding up, the time is technically being rounded to 24:00:00, or 12:00:00 AM on 12/31/1899. To solve this issue, I only rounded in the downward direction to the nearest half hour:
First Response Time =
VAR Hour = HOUR(Metrics[SLA First Response Date/Time])
VAR Minute = MINUTE(Metrics[SLA First Response Date/Time])
RETURN IF(Minute<30,TIME(Hour,0,0),TIME(Hour,30,0))
Hi @2beefd ,
Try creating a calculation column to convert the value to text:
Column = FORMAT('Calendar'[Date] , "hh:nn:ss AM/PM")
Wish it is helpful for you!
Best Regards
Lucien
@2beefd
If the data type is time and you have applied the formatting as "h:nn:ss AM/PM" then you should only see the time values in the X axis.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
61 | |
55 |
User | Count |
---|---|
179 | |
108 | |
105 | |
71 | |
70 |