Forum Discussion

kattlees's avatar
kattlees
Icon for Post Patron rankPost 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
Icon for Solution Sage rankSolution 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
Icon for Post Patron rankPost 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
    Icon for Solution Sage rankSolution 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
      Icon for Post Patron rankPost 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)
      ) 

      • v-xjiin-msft's avatar
        v-xjiin-msft
        Icon for Solution Sage rankSolution Sage
        8 years ago

        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.