Converted workitems in Azure DevOps

[Anonymous]

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.

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)

QTR	MONTH/YEAR	Created Support Cases	How many were converted to Defects?	How many of those Defects have been passed as Defects?	How many have not been assessed?
Q3FY19	Jan-19	32	15	10	2
Q3FY19	Feb-19
Q3FY19	Mar-19
Q4FY19	Apr-19

any help would be appreciated

[v-frfei-msft]

Hi @Anonymous,

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

[Anonymous]

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