Recurrent checks possible?

[BramSegers]

Hi,

I'm struggling for a few days with a problem, which I hope can be resolved here 

I've got a dataset with different traces, including for each trace a Vehicle Codes, Driver code, Date / Time. The most left image is the complete dataset, filtered out with the columns I need & sorted so the traces are sorted descending per vehicle (+ an added index). 

The second screenshot are the ones with the traces that interests me: being 1 (logged in), 2 (logged out) & 44. 
What i try to accomplish is an overview for each logged in driver code of the trace 44. So far so good, but now comes the first tricky part: When i come across a trace type 44, I have to check all traces preceding the last 44 trace to find which driver was logged in at the moment the trace 44 appeared (since the last one). The second, and most likely the most trickiest part: there can be more than one logged in driver (see third right upper screenshot), so the trace 44 applies to both.  The right bottom screenshot is the first display of what I want to achieve. 

I managed to get via the Group By function in the Query stage to get a list of all logged in drivers for the vehicle, but i didn't managed yet to get it working for the trace-to-trace functionallity and not for the current logged in drivers, just all of them who happened to have logged in at least once.

Any help would be very appreciated! 

Thanks in advance

EDIT: Dataset

[Anonymous · Accepted solution]

Hi @BramSegers,

Actually, I still not so sure for your summarize logic, can you please explain more about this with some expect result?

I create a summarize table with ranking column to summarize your records, below is the sample formulas.

Calculate table:
Table = 
VAR temp =
    SUMMARIZE (
        ADDCOLUMNS (
            FILTER ( data, [type] IN { 1, 2} ),
            "DateTime", DATEVALUE ( [Datum] ) + TIMEVALUE ( [Tijdstip] )
        ),
        [DateTime],
        [Voertuig Korte Naam],
        [Action Code],
        [type]
    )
RETURN
    FILTER (
        ADDCOLUMNS (
            temp,
            "Count", COUNTX (
                FILTER (
                    temp,
                    [Action Code] = EARLIER ( [Action Code] )
                        && [Voertuig Korte Naam] = EARLIER ( [Voertuig Korte Naam] )
                ),
                [type]
            )
        ),
        [Count] > 1
    )

Calculate column:
Rank = 
COUNTROWS(
    FILTER (
        ALLSELECTED ( 'Table' ),
        [Voertuig Korte Naam] = EARLIER( 'Table'[Voertuig Korte Naam] )
            && ISONORAFTER (
                    [DateTime], EARLIER ( 'Table'[DateTime] ), ASC,
                    [Count], EARLIER ( 'Table'[Count] ), ASC
            )
    )
)

Measure:
FirstRank = 
var list=CALCULATETABLE(VALUES('Table'[Rank]),ALLSELECTED('Table'),VALUES('Table'[Voertuig Korte Naam]))
return
if( MAX('Table'[Rank]) = MINX(list,[Rank]),"Y","N")

Regards,

Xiaoxin Sheng

[BramSegers]

Thanks, with this help I found a way out! 

[Greg_Deckler]

Can you provide sample text data and expected output? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490