Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
emilygeist
New Member

Constructing Odata Query for Test Runs Per Tester Over Dynamic Timeframe

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"

 

 

1 REPLY 1
Omid_Motamedise
Memorable Member
Memorable Member

Instead of filtering, you can use group by to summerize the rows

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors