Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
PowerBiNooby
New Member

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

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)

JobNumberOperatorDateTimeActivityFirstOperatorLastOperator
1Bob19/1/22 10:21Offer10
1Bob19/1/22 10:22Offer00
1Jane20/1/22 09:11Offer01
2Fred22/2/22 07:12Offer10
2Rose22/2/22 08:13Offer01
3Rose24/3/22 09:30Offer10
3Rose24/3/22 09:30Offer10
3Kate28/4/22 11:12Offer01
4David29/5/22 13:20Offer11

 

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

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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.

View solution in original post

6 REPLIES 6
PowerBiNooby
New Member

@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

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.

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
New Member

@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?

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
Super User
Super User

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors