Help with getting best race pace for each driver

[manelfera]

I am using power bi to extract some information of best lap times for my sim racing league. 

Now I want to know how can I get the best race pace for each of the drivers.

The race pace is considered when a driver has 3 consecutive laps without cuts. I tried to create a calculated column on which store the time for each row + the 2 following times, but I haven't succeeded.

An example of how the lap times are stored is the following table:

Date	LapTimeMillis	Driver	Cuts	Race pace
01/12/2022 22:00	85000	Driver A	0	null (no 3 consecutive clean laps)
01/12/2022 22:02	86000	Driver A	1	null (no 3 consecutive clean laps)
01/12/2022 22:04	85000	Driver A	0	(85000 + 87000 + 86000)/3
01/12/2022 22:04	84000	Driver B	0	(84000 + 86000 + 85000)/3
01/12/2022 22:06	86000	Driver B	0	null (no 3 consecutive clean laps)
01/12/2022 22:07	87000	Driver A	0	null (no 3 consecutive clean laps)
01/12/2022 22:08	85000	Driver B	0	null (no 3 consecutive clean laps)
01/12/2022 22:09	86000	Driver A	0	null (no 3 consecutive clean laps)

Date column is used to sort laps for each driver.

Anyone can help with this?

Sample data:

https://1drv.ms/x/s!AmH97gz_o34L-aVirzuEbdYqGy6S8A?e=pflRp5 

[manelfera]

I share here a copy of the table on which I am working, so there is more data and might be easy to have a solution.

https://1drv.ms/x/s!AmH97gz_o34L-aVirzuEbdYqGy6S8A?e=pflRp5 

The column Lap is the number of lap of each driver, as I have sorted them and set the number. So there might be an easy way with lap number, cuts, laptime (or laptimemillis) and driver. But I don't know what to do.

[ppm1]

Please try this measure expression. The RAND is there to break any ties in case two laps have exact same time (so only 3 rows included in average).

Avg Last 3 No Cuts =
VAR vThisDT =
    MAX ( Laps[Date] )
VAR vLastCutTime =
    CALCULATE (
        MAX ( Laps[Date] ),
        ALLEXCEPT ( laps, Laps[Driver] ),
        Laps[Date] <= vThisDT,
        Laps[Cuts] > 0
    )
VAR vTop3NoCuts =
    CALCULATETABLE (
        TOPN ( 3, Laps, Laps[LapTimeMillis], ASC, RAND (), ASC ),
        ALLEXCEPT ( laps, Laps[Driver] ),
        Laps[Date] > vLastCutTime
            && Laps[Date] <= vThisDT
    )
VAR vResult =
    IF (
        COUNTROWS ( vTop3NoCuts ) = 3,
        AVERAGEX ( vTop3NoCuts, Laps[LapTimeMillis] ),
        "Not 3 laps"
    )
RETURN
    vResult

Pat

[manelfera]

Hi, I have 2.000 records with data from 9 drivers and it's just calculating results for 3 drivers, for the others it's not giving any output, and they have consecutive valid laps. What might be wrong?

I have updated what you told me to this, so I could add as a column. Having it as a measure it only returns me 2 values with valid laps.

AvgRacePaceMillis = 
VAR vThisDT =
    MAX ( 'Main Results'[Timestamp] )
VAR vLastCutTime =
    CALCULATE (
        MAX ( 'Main Results'[Timestamp] ),
        ALLEXCEPT ( 'Main Results', 'Main Results'[Drivers.name] ),
        'Main Results'[Timestamp] <= vThisDT,
        'Main Results'[Cuts] > 0
    )
VAR vTop3NoCuts =
    CALCULATETABLE (
        TOPN ( 3, 'Main Results', 'Main Results'[LapTimeMillis], ASC, RAND (), ASC ),
        ALLEXCEPT ( 'Main Results', 'Main Results'[Drivers.name] ),
        'Main Results'[Timestamp] > vLastCutTime
            && 'Main Results'[Timestamp] <= vThisDT
    )
VAR vResult =
    IF (
        COUNTROWS ( vTop3NoCuts ) = 3,
        AVERAGEX ( vTop3NoCuts, 'Main Results'[LapTimeMillis] ),
        BLANK()
    )
RETURN
    vResult

Also, in this particular case if 2 valid laps have the same time it doesn't matter, it is ok to sum 3 valid laps even if 2 have the same exact time.