Activities table linked to Job table - Find earliest activity for each Job

[PowerBiNooby]

Hi all, HELP!

I have 2 tables. Activities and Jobs. We have loads of Jobs that have multiple activities like Create, Offer, Accept, Approve, Hold, Close. Each activity is date/time stamped and the Operator performing the activity is logged too. 

I want to look at how often a Job is started by an operator and finished by another. To do this I need to identify which operator performed the earliest/last Offer activity (Offer can be done multiple times until it is Accepted)...

I can return the Operator when a condition is met, I just don't know how to identify the first Operator to Offer per Job? Note, you could have an operator Offer 2 or more instances at the exact same time, I just want to mark who made the earliest Offer and the lastest Offer.

So - the Data looks like this... (the last 2 columns are what I need, they currently do not exist)

JobNumber	Operator	DateTime	Activity	FirstOperator	LastOperator
1	Bob	19/1/22 10:21	Offer	1	0
1	Bob	19/1/22 10:22	Offer	0	0
1	Jane	20/1/22 09:11	Offer	0	1
2	Fred	22/2/22 07:12	Offer	1	0
2	Rose	22/2/22 08:13	Offer	0	1
3	Rose	24/3/22 09:30	Offer	1	0
3	Rose	24/3/22 09:30	Offer	1	0
3	Kate	28/4/22 11:12	Offer	0	1
4	David	29/5/22 13:20	Offer	1	1

I've seen how to identify the earliest in a whole list but not the earliest by JobNumber (or any sub grouping)

Many thanks in advance

PN

[PowerBiNooby]

@johnt75 

Since publishing the new(with the calculated First Operator etc..) dataset I've found that I'm getting a DAX error...

"A table of multiple values was supplied where a single value was expected".

Am I doing something wrong by publishing this and using the data across multiple reports? It's the exact same error for the first operator and last operator columns. 

Sorry... I have tried to find a solution to this error but I think my lack of understanding about calculatetable is hindering me.

thanks again

[johnt75]

It looks like you have multiple entries with the exact same datetime for the same job. You will need to add another column to the TOPN calculation to act as a tie breaker. If each row has a unique identifier then you could use that, like 

TOPN ( 1, 'Table', 'Table'[DateTime], ASC, 'Table'[Unique ID], ASC ),

If you don't have a unique identifier for each row then you could add one in Power Query. Sort the table by the datetime column and then add an index column.

[PowerBiNooby]

I had added an index using...

IndexByDate =

RANKX ( Activities, Activities[Date],, ASC, DENSE )

It didn't work... so I just now used PowerQ in Transform Data to do it at load... used the new index and BOOM, working!

Thank you so so much.

[PowerBiNooby]

@johnt75 That's GREAT! It worked beautifully in the Activities table. For each line there is now an Operator name for the earliest entry of an Offer. AMAZING. Thank you.

However, now I see it I'm thinking that what I'd like is that have only 1 of each per JobNumber in the Jobs table. 

I want to have the first and last Operator to make an offer in the Jobs table, not the activities table. Can I run the same DAX for a new column in the Jobs table?

[johnt75]

If you have a relationship from the Jobs table to the Activities table then you should be able to add the job number from the Jobs table into a visual and use the same measures you have now. No need to create new measures

[johnt75 · Accepted solution]

You can get the first operator with a measure like

First Operator =
SELECTCOLUMNS (
    CALCULATETABLE (
        TOPN ( 1, 'Table', 'Table'[DateTime], ASC ),
        ALLEXCEPT ( 'Table', 'Table'[JobNumber] ),
        'Table'[Activity] = "Offer"
    ),
    "@name", 'Table'[Operator]
)

and for the last operator just change ASC to DESC.