Is there a way to assign a sequence # column to a table within powerbi based on data in several col?

[MICOLSCOTT]

I'm relatively new working with PowerBI and know how I would do this in SQL, but in this instance I already have the data set in a PowerBI table and wondered if there is a way to do this within PowerBI.

SQL equivalent: ROW_NUMBER() OVER(PARTITION BY WORK_DATE, EMP_ID) ORDER BY TASK_START_TS, EMP_ID, WORK_DATE ASC)

Example: I have a table 'TASKS' with data like below, which has employees and a list of their tasks for the day with the time they started each task.

EMP_ID	TASK_ID	TASK_TYPE	WORK_DATE	TASK_START_TS	TASK_COMPLETE_TS
emp071	286511	Service	11/12/2019	11/12/19 12:15 PM	11/12/19 3:00 PM
emp071	286564	Repair	11/12/2019	11/12/19 8:00 AM	11/12/19 10:00 AM
emp003	286732	Service	11/13/2019	11/13/19 8:00 AM	11/13/19 9:20 AM
emp003	286771	Repair	11/12/2019	11/12/19 8:00 AM	11/12/19 11:00 AM
emp003	286794	Repair	11/12/2019	11/12/19 11:30 AM	11/12/19 3:00 PM
emp071	286795	Repair	11/13/2019	11/13/19 11:45 AM	11/13/19 2:00 PM
emp071	286954	Service	11/13/2019	11/13/19 8:00 AM	11/13/19 11:00 AM
emp071	386145	Service	11/12/2019	11/12/19 10:01 AM	11/12/19 12:10 PM
emp003	395614	Service	11/13/2019	11/13/19 1:00 PM	11/13/19 7:00 PM
emp003	453262	Repair	11/13/2019	11/13/19 9:30 AM	11/13/19 12:30 PM

I want to know if there is a way to make a calculated column that will assign a sequence number for each task by EMP_ID and WORK_DATE and order by the TASK_START_TS timestamp in ascending order. Call the column TASK_SEQ_NUM, 1st task for the employee on that work date as 1, second task for the employee on that work date as 2, and so on. If there's a link to a tutorial or something I'd love to check it out. I have tried googling it all morning and haven't had much luck. Thanks

[v-juanli-msft · Accepted solution]

Hi @MICOLSCOTT 

Create a calculated column

Column =
RANKX (
    FILTER (
        'Table',
        'Table'[WORK_DATE] = EARLIER ( 'Table'[WORK_DATE] )
            && 'Table'[EMP_ID] = EARLIER ( 'Table'[EMP_ID] )
    ),
    [TASK_START_TS],
    ,
    ASC,
    DENSE
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MICOLSCOTT]

Worked perfectly! Thank you @v-juanli-msft !