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

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.

      • kattlees's avatar
        kattlees
        Post Patron
        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)
        )