cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBi_Batman Helper III
Helper III

Converted workitems in Azure DevOps

for anyone who is familiar with VSTS and powerbi will hopefully understand the below

 

with every major update to a work item in VSTS, Powerbi will duplicate the rows for each individual update to provide a instance of the details

this is an example of the the same workitem ID but has multiple rows of data.

 

Info.png

my current needs are as such

1. count per month the created count of items that come in as a workitem type (Support). this would be achieved easily in a graph of count per month based on created date column in screenshot

2. of those counts in point 1, how many were converted to a workitem type (Defect). i can obtain a list of these items but cannot get a count. essentially the list is based on "First Workitem Type" is 'Defect' and "Last Workitem Type" is 'Support'. Is there a way to achieve a count per month? i cannot seem to work out the correct measure/formula to do so

3. how many of those items in point 3 have not been rejected once they have been triaged. this would be based on the field/column "State"

 

i would assume i need multiple measures, but i am not sure hwhere to start

 

my output table would need to be something like this  (IF POSSIBLE)

 QTRMONTH/YEARCreated Support CasesHow many were converted to Defects?How many of those Defects have been passed as Defects?How many have not been assessed?
Q3FY19Jan-193215102
Q3FY19Feb-19    
Q3FY19Mar-19    
Q4FY19Apr-19    

 

any help would be appreciated

2 REPLIES 2
Community Support
Community Support

Re: Converted workitems in Azure DevOps

Hi @PowerBi_Batman,

 

Could you please share your sample data to me, if you don't have confidential data. Please upload the file to One Drive and share the link here.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
PowerBi_Batman Helper III
Helper III

Re: Converted workitems in Azure DevOps

Hi @v-frfei-msft

 

here is the link. ive exported a sample of the data into excel and uploaded to google drive

https://drive.google.com/open?id=1_XGGYuKkL8MhgyVD3b4f_6_3hS9Jm7mc

 

this is one instance of a workitem (see work item ID) that has numerous updates

in one the rows of data, the work item type was changed from Support to Defect

isCurrent column will mark the item with Yes to inform you of the current version of the data

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors