Highlight or filter rows based on hour values

[WilliamAzevedo]

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:

• First info of the day and last info of the morning and
• First info of the afternoon and last info 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!

[WilliamAzevedo]

Since we're here: if my table has multiple dates, is it possible to apply this on each date?

[v-jiewu-msft · Accepted 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.

[WilliamAzevedo]

It worked greatly! Thank you very much!

[WilliamAzevedo]

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!