Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

How caclculate TouchTime for Azure DevOps

Hello

 

i want to calc Touch Time (sum working time by closed items per period in certain State). In my data set i have:
Work Item Id - item
State Time in Days - time in certain State
State - State of work item
is_last_day_in_state - True/False
weeknum - number of week (function from date)
I have mease for calc, but problem this measure calculate all items for period (closed and openned).

I think for resolve this task need:
1. find work items Id which was closed
2. use list of closed work items Id for calcluate Touch Time

How does it can apply in DAX measure?

Need Help

6 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

     

    Are your fields in the same table? If so, you only need to add the Filter() function to the measure to filter out the closed items.

     

    If this problem still can not be solved, please show me the sample data.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello

     

    yes, fields in same table.

     

    If I add to Filter "State" = "Closed" then will be incorrect, becase display measure will be calculte items with STATE={...}, but i want run measure ONLY for closed items. For example, i manual set filter by State = "Closed" , "In progress/Analytic", and see many items (some item have state=closed, some not, it means what filtering see different items closed and opened):

     

     

    i think the first need filtereing closed item, for example list of closed items:

    and futher use Work Item Id from this list for measure.

    May be create new table with closed items and add additional condional to mesure [work item id] = table_closed_items[work item id]...but i don't know how does it do...

     

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Like this? If it's incorrect, please show me the sample data and expected output table like me.

    Measure = 
CALCULATE(
    SUM(Sheet2[Working time]),
    FILTER(
        Sheet2,
        [State] = "Closed" && [Weeknum] = MAX([Weeknum])
    )
)

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      isn't correct, i said closed items located in another table, need condtion for comapare source table (Id) = table with closed (Id)

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Do you mind sharing me a sample .pbix file?

    I need a complete sample data model.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

  • Iryna_K's avatar
    Iryna_K
    Frequent Visitor
    2 years ago

    Install the Time in State for Azure DevOps extension and follow this guide to calculate Touch Time. Also, you can download these reports to Excel and further manipulate the data there.

    1. Select a query for the report generation

    2. Select a calendar for the right calculation

    3. Filter Work Items by Closed

    4. (Optionally) Select an items range in the “Work Item“ dropdown (to display items that were closed in such period)

    5. Select a period in the “Time range” dropdown (to display the time calculation for the selected item range in the specific time range)

    6. Look at the State column and the time that item was in such state.

     

    I hope it will be helpful for you!