Adding sequence column based on time and surgeon

[kattlees]

If I have a table that lists surgeon name, OR, Start Time like this:

JONES JANE	OR 6	9:00
JONES JANE	OR 5	7:45
JONES JANE	OR 6	7:00
JONES JANE	OR 5	11:45
JONES JANE	OR 6	11:00
JONES JANE	OR 5	10:15
LOU BETTY	OR 4	10:30
LOU BETTY	OR 4	8:30
LOU BETTY	OR 3	15:15
LOU BETTY	OR 4	7:30
LOU BETTY	OR 3	11:30
LOU BETTY	OR 4	12:00
LOU BETTY	OR 3	9:30

How do I get a "sequence column" added that would put a number starting at 1 that shows the order of surgeries.

Would wind up looking like this?

JONES JANE	OR 6	9:00	3
JONES JANE	OR 5	7:45	2
JONES JANE	OR 6	7:00	1
JONES JANE	OR 5	11:45	6
JONES JANE	OR 6	11:00	5
JONES JANE	OR 5	10:15	4
LOU BETTY	OR 4	10:30	4
LOU BETTY	OR 4	8:30	2
LOU BETTY	OR 3	15:15	7
LOU BETTY	OR 4	7:30	1
LOU BETTY	OR 3	11:30	5
LOU BETTY	OR 4	12:00	6
LOU BETTY	OR 3	9:30	3

[v-xjiin-msft]

Hi @kattlees,

To achieve your requirement, you can try following calculated column.

Rank =
RANKX (
    CALCULATETABLE ( surgeon, ALLEXCEPT ( surgeon, surgeon[surgeon] ) ),
    surgeon[Start Time],
    ,
    ASC,
    DENSE
)

Thanks,
Xi Jin.

[kattlees]

This works great but I forgot one thing - how would you do it by date, time?

So rank surgeries for Surgeon A yesterday 1-? and surgeries for surgeon A today 1-?

[v-xjiin-msft]

Hi @kattlees,

What did you mean date, time? A column in datetime type? Or two columns date and time?

Could you please share us some sample data if possible?

Thanks,
Xi Jin.

[kattlees · Accepted solution]

I was able to figure out my own answer by piecing together other answers.

I gave each case of the day a rank by surgeon:

PreviousRowSales =
VAR Index = Table1[Rank] - 1
RETURN
    CALCULATE (
        SUM ( Table1[Current Month Sales] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[Customer] ), Table1[Rank] = Index )
    )

Then got this calculated column:

PreviousMDOut =
VAR Index = V_EWS_TASK_RECORD[Rank] - 1
VAR SDATE = V_EWS_TASK_RECORD[OR Times Final.SCHDT]
RETURN
CALCULATE (
SUM ( V_EWS_TASK_RECORD[MD out] ),
FILTER ( ALLEXCEPT ( V_EWS_TASK_RECORD, V_EWS_TASK_RECORD[OR Times Final.SURGEON] ), V_EWS_TASK_RECORD[Rank] = Index && V_EWS_TASK_RECORD[OR Times Final.SCHDT]=SDATE)
) 

[v-xjiin-msft]

Hi @kattlees,

I'm gald to hear that you have resolved your issue. Please kindly mark your solution as answer. It will help others with relevant issue find the answer more easily.

Thanks,
Xi Jin.

[Greg_Deckler]

This one does it by Surgeon:

Rank3 = RANKX(FILTER(ALL(Surgeries),[Surgeon]=EARLIER([Surgeon])),[Time],,ASC)

[Greg_Deckler]

This one does it by OR:

Rank2 = RANKX(FILTER(ALL(Surgeries),[OR]=EARLIER([OR])),[Time],,ASC)

[Greg_Deckler]

As a column in your table:

Rank = RANKX(Surgeries,[Time],,ASC)