Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |