Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have few Request types. We can have have their status as Success, Failed, Expired.
I have these timestamps round the clock and I would like to filter the time between morning 10AM to 12PM in to 15 minutes intervals and group them in to categories like 10:00-10:15, 10:15-10:30. How do i do that ?
Is there any default slicer for time ? I am aware that date has but could not find time slicer.
Example:
REQUEST | STATUS | UPDATED_TS |
MoveIn | Success | 14-SEP-2018 05:29 |
MoveIn | Success | 14-SEP-2018 05:20 |
MoveIn | Success | 14-SEP-2018 05:26 |
MoveIn | Success | 14-SEP-2018 05:19 |
MoveIn | Success | 14-SEP-2018 05:23 |
MoveIn | Success | 14-SEP-2018 05:26 |
MoveIn | Success | 14-SEP-2018 05:35 |
MoveIn | Success | 14-SEP-2018 05:32 |
Solved! Go to Solution.
Unfortunately, there is no specific slicer for time. I can propose you to create a calculated column in the same table where you have datetime column with calculation below:
TimeGroup = FORMAT(HOUR(Table2[Datetime column]),"00")
& ":" & FORMAT((ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15),"00") & "-" & FORMAT(HOUR(Table2[Datetime column]),"00") & ":" & FORMAT((ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15+15),"00")
Where
- Table2[Datetime column] is a table and column of your datetime
Then you can add a simple slicer with this field.
Then it will look like below:
Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
I understand your point. See an updated approach:
Create 5 calculated columns:
HoursStart = Hour(Table2[Datetime column]) MinutesStart = ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15 HoursEnd = IF(Table2[MinutesStart]=45,Hour(Table2[Datetime column])+1,Hour(Table2[Datetime column])) MinutesEnd = IF(Table2[MinutesStart]+15=60,0,Table2[MinutesStart]+15) TimeGroup = FORMAT(Table2[HoursStart],"00")
& ":"
& FORMAT(Table2[MinutesStart],"00")
& "-"
& FORMAT(Table2[HoursEnd],"00")
& ":"
& FORMAT(Table2[MinutesEnd],"00")
Then it will behave as below:
Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
Unfortunately, there is no specific slicer for time. I can propose you to create a calculated column in the same table where you have datetime column with calculation below:
TimeGroup = FORMAT(HOUR(Table2[Datetime column]),"00")
& ":" & FORMAT((ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15),"00") & "-" & FORMAT(HOUR(Table2[Datetime column]),"00") & ":" & FORMAT((ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15+15),"00")
Where
- Table2[Datetime column] is a table and column of your datetime
Then you can add a simple slicer with this field.
Then it will look like below:
Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
Thanks so much for help, but how do we modify 60's. Eg: 9:60. 10:60 to 10:00, 11:00 respectively
10:60? What do you mean? For 10 hours maximum is 10:59. Provide more details.
Regards,
Ruslan
I mean as per your logic, The grouping is done properly, but for times between HH:45 to HH:60 they are getting displayed as 09:45 - 09:60
10:45-10:60
Is there a way that we can categorize them as 09-45-10:00 etc for all the time stamps.
I understand your point. See an updated approach:
Create 5 calculated columns:
HoursStart = Hour(Table2[Datetime column]) MinutesStart = ROUNDDOWN(MINUTE(Table2[Datetime column])/15,0)*15 HoursEnd = IF(Table2[MinutesStart]=45,Hour(Table2[Datetime column])+1,Hour(Table2[Datetime column])) MinutesEnd = IF(Table2[MinutesStart]+15=60,0,Table2[MinutesStart]+15) TimeGroup = FORMAT(Table2[HoursStart],"00")
& ":"
& FORMAT(Table2[MinutesStart],"00")
& "-"
& FORMAT(Table2[HoursEnd],"00")
& ":"
& FORMAT(Table2[MinutesEnd],"00")
Then it will behave as below:
Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |