Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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"
Instead of filtering, you can use group by to summerize the rows
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
69 | |
23 | |
18 | |
13 |