Forum Discussion

blazko's avatar
blazko
Helper III
7 years ago

time axis, gaps calculation

hi, I want to calculate break time (duration longer than 5mins). I want to do this with a measure, not a table.
I've put worker activity (blue) on a chart (time axis bind in 5 mins), also made a marker (orange) that indicates non-activity. I would then simply count orange bars, but the ptoblem is i dont know how to exlude the bars before start time (min activity time ) and after finish time (max activity time). File attached. It would be nice to have it in one measue, in original table model theres a worker id column, I want to aggregate, and sum breaks (count occurance ->dont know how yet, and sum time) by a worker.



https://drive.google.com/open?id=1kdFYizQ-ze4i6JLVStkmRUKnMsItbF9c

Need Help

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi blazko ,

     

    how to exlude the bars before start time (min activity time ) and after finish time (max activity time)

    You could apply "Visual level filters" to exclude those times beyond expected time range.

     

    It would be nice to have it in one measue, in original table model theres a worker id column, I want to aggregate, and sum breaks (count occurance ->dont know how yet, and sum time) by a worker.

    I am not very clear about your desired output. Please eloborate with examples.

     

    Regards,

    Yuliana Gu

    • blazko's avatar
      blazko
      Helper III
      7 years ago

      Visual level filters are not the solution in what I need.
      I want to calculate daily avtivity gaps, lets say number of orange bars, but between min and max activity time, that is start and end of workday (which arent static).

      I got a little bit inspired with latest Guy in a Cube YT video with Alberto Ferrari where he could mark continous values. Thats what i try to do. If theres a time value with no activity (orange) i want to aggregate and sum.
      So in the final output:

      for ex user, I want to calulate number of no-activity time durations (breaks), list them (ex 35 min, 15 mins, 35mins), sum them by a duration (from prev ex 35 +15 +35).

    • blazko's avatar
      blazko
      Helper III
      7 years ago

      Im trying somthing with calculated table as a variable, but my head is to small for this :) Still have an issue with exluding rows aftre and before start and end of daily activity.

      For more explanation what i want (first):


      done with:

      work_gap_marker = 
var summ= SUM('log'[qty])
return
IF(summ=0;1;0)