Hello, I am new to PowerBI and have tried for a while to figure this out myself but am not having any luck. Here is the information:
Goal: Make a report that shows the test runs per tester over a dynamic time range. So I can open the report and see how many tests each one of the testers has completed over the past month, week, day, etc. (It can be separate queries/graphs for each time period). I'm imagining a bar graph that has the testers listed on the x-axis and the sum of tests completed during that time frame on the y-axis.
The data is stored in Azure Devops.
I tried to use TestPointHistorySnapshot and thought I was close, but there were way more test runs showing up than were actually run (it was showing like 10 test runs for a single tester for one day when in reality they only had 4 runs), so I am not sure if I'm understanding this entity type correctly and what the "date" refers to in that entity type. I thought it was because it was taking multiple "snapshots" per day, so I then filtered it to exclude duplicate TestCaseIDs during the same day, but that did not fix the problem. Maybe I have to join another table and instead use CompletedDate? Here is the query I had. Any guidance would be appreciated. Thank you.
let
// Define your organization and project
Organization = "blah",
Project = "blah",
// Construct the OData URL to retrieve data from TestPointHistorySnapshot
ODataUrl =
"https://analytics.dev.azure.com/" & Organization & "/" & Project & "/_odata/v3.0/TestPointHistorySnapshot?"
& "$expand=Tester($select=UserName),Date"
& "&$select=Date,ResultOutcome,TestCaseId,Tester"
& "&$filter=(Tester ne null) and (ResultOutcome ne 'None')",
// Fetch data from the OData feed
Source = OData.Feed(ODataUrl, null, [Implementation = "2.0"]),
// Expand the Tester column to get the UserName
#"Expanded Tester" = Table.ExpandRecordColumn(Source, "Tester", {"UserName"}, {"Tester.UserName"}),
// Expand the Date column to get the Date.Date field
#"Expanded Date" = Table.ExpandRecordColumn(#"Expanded Tester", "Date", {"Date"}, {"Date.Date"}),
// Change the type of Date.Date to date
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Date", {{"Date.Date", type date}}),
// Date Filter
CurrentDate = Date.From(DateTime.LocalNow()),
OneMonthAgoDate = Date.AddMonths(CurrentDate, -1),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date.Date] >= OneMonthAgoDate),
// Remove duplicates based on TestCaseId and Date.Date
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"TestCaseId", "Date.Date"})
in
#"Removed Duplicates"
