Forum Discussion

kattlees's avatar
kattlees
Post Patron
8 years ago
Solved

Adding sequence column based on time and surgeon

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

 

JONES JANEOR 69:00
JONES JANEOR 57:45
JONES JANEOR 67:00
JONES JANEOR 511:45
JONES JANEOR 611:00
JONES JANEOR 510:15
LOU BETTYOR 410:30
LOU BETTYOR 48:30
LOU BETTYOR 315:15
LOU BETTYOR 47:30
LOU BETTYOR 311:30
LOU BETTYOR 412:00
LOU BETTYOR 39: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 JANEOR 69:003
JONES JANEOR 57:452
JONES JANEOR 67:001
JONES JANEOR 511:456
JONES JANEOR 611:005
JONES JANEOR 510:154
LOU BETTYOR 410:304
LOU BETTYOR 48:302
LOU BETTYOR 315:157
LOU BETTYOR 47:301
LOU BETTYOR 311:305
LOU BETTYOR 412:006
LOU BETTYOR 39:303
Need Help
  • kattlees's avatar
    kattlees
    8 years ago

    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)
    ) 

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    This one does it by OR:

     

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

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    This one does it by Surgeon:

     

    Rank3 = RANKX(FILTER(ALL(Surgeries),[Surgeon]=EARLIER([Surgeon])),[Time],,ASC)
  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    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's avatar
      kattlees
      Post Patron
      8 years ago

      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's avatar
        v-xjiin-msft
        Solution Sage
        8 years ago

        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.