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!
I have a table that stores data about car, drivers, dates, hours and locations/itineraries. The system that manages that data emits reports based on date, which gives me hundreds of lines per date and I would like to have it resumed in two periods of the day:
About that: it is expected that the driver stops at 12:00 and restart at 13:30 (give or take 15 minutes) but, much more than I would like, they don't follow that directive. So, in addition to the idea of last of the mornin and first of the afternoon, i would like to set a timespan of, let's say, 50 minutes between these two moments. Examples:
What I have:
Motorista | Data | Início | Fim | Placa | Localização |
Driver | 04/04/2024 | 10:16 | 10:42 | Plate | Location |
Driver | 04/04/2024 | 10:16 | 10:42 | Plate | Location |
Driver | 04/04/2024 | 10:16 | 10:42 | Plate | Location |
Driver | 04/04/2024 | 11:49 | 12:17 | Plate | Location |
Driver | 04/04/2024 | 11:49 | 12:17 | Plate | Location |
Driver | 04/04/2024 | 14:04 | 14:06 | Plate | Location |
Driver | 04/04/2024 | 14:08 | 14:32 | Plate | Location |
Driver | 04/04/2024 | 14:08 | 14:32 | Plate | Location |
Driver | 04/04/2024 | 14:33 | 14:34 | Plate | Location |
Driver | 04/04/2024 | 14:33 | 14:34 | Plate | Location |
Driver | 04/04/2024 | 14:35 | 15:18 | Plate | Location |
Driver | 04/04/2024 | 14:35 | 15:18 | Plate | Location |
Driver | 04/04/2024 | 15:44 | 16:05 | Plate | Location |
Driver | 04/04/2024 | 15:44 | 16:05 | Plate | Location |
Driver | 04/04/2024 | 16:07 | 16:51 | Plate | Location |
Driver | 04/04/2024 | 16:07 | 16:51 | Plate | Location |
Driver | 04/04/2024 | 16:55 | 17:24 | Plate | Location |
Driver | 04/04/2024 | 16:55 | 17:24 | Plate | Location |
Driver | 04/04/2024 | 16:55 | 17:24 | Plate | Location |
What I would like:
Motorista | Data | Início | Fim | Placa | Localização |
Driver | 04/04/2024 | 10:16 | 10:42 | Plate | Location |
Driver | 04/04/2024 | 10:16 | 10:42 | Plate | Location |
Driver | 04/04/2024 | 10:16 | 10:42 | Plate | Location |
Driver | 04/04/2024 | 11:49 | 12:17 | Plate | Location |
Driver | 04/04/2024 | 11:49 | 12:17 | Plate | Location |
Driver | 04/04/2024 | 14:04 | 14:06 | Plate | Location |
Driver | 04/04/2024 | 14:08 | 14:32 | Plate | Location |
Driver | 04/04/2024 | 14:08 | 14:32 | Plate | Location |
Driver | 04/04/2024 | 14:33 | 14:34 | Plate | Location |
Driver | 04/04/2024 | 14:33 | 14:34 | Plate | Location |
Driver | 04/04/2024 | 14:35 | 15:18 | Plate | Location |
Driver | 04/04/2024 | 14:35 | 15:18 | Plate | Location |
Driver | 04/04/2024 | 15:44 | 16:05 | Plate | Location |
Driver | 04/04/2024 | 15:44 | 16:05 | Plate | Location |
Driver | 04/04/2024 | 16:07 | 16:51 | Plate | Location |
Driver | 04/04/2024 | 16:07 | 16:51 | Plate | Location |
Driver | 04/04/2024 | 16:55 | 17:24 | Plate | Location |
Driver | 04/04/2024 | 16:55 | 17:24 | Plate | Location |
Driver | 04/04/2024 | 16:55 | 17:24 | Plate | Location |
Or:
Motorista | Data | Início | Fim | Placa | Localização |
Driver | 04/04/2024 | 10:16 | 10:42 | Plate | Location |
Driver | 04/04/2024 | 11:49 | 12:17 | Plate | Location |
Driver | 04/04/2024 | 14:04 | 14:06 | Plate | Location |
Driver | 04/04/2024 | 16:55 | 17:24 | Plate | Location |
Thank you in advance for any help!
Solved! Go to Solution.
Hi @WilliamAzevedo ,
Based on my testing, please try the following methods:
1.Create the simple table.
2.Open the Power Query editor, select Inicio column and choose Remove duplicates.
3.Close and Apply. Create the new column to filter the Time.
4.Create the new measure to highlight the rows.
Highlight morning =
var hour_ = SELECTEDVALUE('Table'[Início])
var min_hour_morning = CALCULATE(MIN('Table'[Início]), ALLSELECTED('Table'),'Table'[Mor after] = "Morning")
var max_hour_morning = CALCULATE(MAX('Table'[Início]),ALLSELECTED('Table'), 'Table'[Mor after] = "Morning")
var min_hour = CALCULATE(MIN('Table'[Início]),ALLSELECTED('Table'), 'Table'[Mor after] = "Afternoon")
var max_hour = CALCULATE(MAX('Table'[Início]),ALLSELECTED('Table'), 'Table'[Mor after] = "Afternoon")
RETURN
IF(min_hour_morning = hour_, "red",
IF(max_hour_morning = hour_, "red",
IF(min_hour = hour_, "red",
IF(max_hour = hour_, "red"
)
)
)
)
5.Select the Hour column and choose conditional formatting.
6.Select the Field value and choose Highlight morning measure.
7.The result is shown below.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Since we're here: if my table has multiple dates, is it possible to apply this on each date?
Hi @WilliamAzevedo ,
Based on my testing, please try the following methods:
1.Create the simple table.
2.Open the Power Query editor, select Inicio column and choose Remove duplicates.
3.Close and Apply. Create the new column to filter the Time.
4.Create the new measure to highlight the rows.
Highlight morning =
var hour_ = SELECTEDVALUE('Table'[Início])
var min_hour_morning = CALCULATE(MIN('Table'[Início]), ALLSELECTED('Table'),'Table'[Mor after] = "Morning")
var max_hour_morning = CALCULATE(MAX('Table'[Início]),ALLSELECTED('Table'), 'Table'[Mor after] = "Morning")
var min_hour = CALCULATE(MIN('Table'[Início]),ALLSELECTED('Table'), 'Table'[Mor after] = "Afternoon")
var max_hour = CALCULATE(MAX('Table'[Início]),ALLSELECTED('Table'), 'Table'[Mor after] = "Afternoon")
RETURN
IF(min_hour_morning = hour_, "red",
IF(max_hour_morning = hour_, "red",
IF(min_hour = hour_, "red",
IF(max_hour = hour_, "red"
)
)
)
)
5.Select the Hour column and choose conditional formatting.
6.Select the Field value and choose Highlight morning measure.
7.The result is shown below.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It worked greatly! Thank you very much!
I thought of part of the solution: using Power Query, I created a column merging "Data", "Placa", "Início" and "Fim"; then i removed the duplicates and the table has got around 90% smaller.
Now I need to find how to remove the rows between start and end of the morning and start and end of the afternoon, or highlight them!
Any help is appreciated and thank you!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
227 | |
127 | |
118 | |
83 | |
78 |