Forum Discussion

Aeolus's avatar
Aeolus
Frequent Visitor
6 years ago
Solved

Index Ranking user with time

Hi Community,

 

I hope you can help me with the following: I want to calculate the time duration of a specific process by a specific user (gebruiker) but have not got further than calculating the time difference between the next process entry.

 

Current setup:

Start Time = VAR Previous = if(Table[GR Index Total]>1;Table[GR Index Total]-1;0) RETURN calculate(max(Table[datum_tijd]);filter(Table;Table[GR Index Total]=Previous))
" the next ret_date entry from the user when they perform a 'goods receipt' action"
 
 End time = ret_date
"the end time of the user initiated 'goods receipt' action"
 
GR Index Total = IF(Table[Process Description]="Goods Receipt";RANKX(FILTER(Table;Table[Process Text]="process_receipt");Table[ID];Table[ID];ASC);0)
"Indexing the goods receipt entries (there are other processes) in order based on ID"

 

 

What should happen: 

 

ID 1291284, user "chefra", process description "Goods Receipt" should have an end time equal to ret_date and a start time equal to ret_date used in ID 1291174 (13:22:10) = time difference of 27 minutes instead of the 19 currently calculated.

 

Result should be a proper bell curve for all users doing goods receipt instead of what i get now (see example)

 

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Aeolus ,

     

    You could update your DAX :

    GR Index Total =
IF (
    Table[Process Description] = "Goods Receipt";
    RANKX (
        FILTER (
            Table;
            Table[Process Text] = "process_receipt"
                && 'Table'[gebruiker] = EARLIER ( 'Table'[gebruiker] )
        );
        Table[ID];
        Table[ID];
        ASC
    );
    0
)
    Start Time =
CALCULATE (
    FIRSTNONBLANK ( 'Table'[ret_dat]; 1 );
    FILTER (
        'Table';
        'Table'[GR Index Total]
            = EARLIER ( 'Table'[GR Index Total] ) - 1
            && 'Table'[gebruiker] = EARLIER ( 'Table'[gebruiker] )
    )
)

     

2 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Aeolus ,

     

    You could update your DAX :

    GR Index Total =
IF (
    Table[Process Description] = "Goods Receipt";
    RANKX (
        FILTER (
            Table;
            Table[Process Text] = "process_receipt"
                && 'Table'[gebruiker] = EARLIER ( 'Table'[gebruiker] )
        );
        Table[ID];
        Table[ID];
        ASC
    );
    0
)
    Start Time =
CALCULATE (
    FIRSTNONBLANK ( 'Table'[ret_dat]; 1 );
    FILTER (
        'Table';
        'Table'[GR Index Total]
            = EARLIER ( 'Table'[GR Index Total] ) - 1
            && 'Table'[gebruiker] = EARLIER ( 'Table'[gebruiker] )
    )
)

     

    • Aeolus's avatar
      Aeolus
      Frequent Visitor
      6 years ago

      Hi V-eachen,

       

      Works like a charm!!

       

      Thank you,

      Aeolus