The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Friends,
I am working with Pass Rate Trends from Azure DevOps Pipelines and following documentation below.
The document is the following query to get data and the query is only for a Single named Pipeline and is already grouped.
let
Source = OData.Feed ("https://analytics.dev.azure.com/{organization}/{project}/_odata/v3.0-preview/PipelineRuns?"
&"$apply=filter( "
&"Pipeline/PipelineName eq '{pipelineName}' "
&"and CompletedDate ge {startdate} "
&"and CanceledCount ne 1 "
&") "
&"/groupby( "
&"(CompletedOn/Date), "
&"aggregate "
&"($count as TotalCount, "
&"SucceededCount with sum as SucceededCount , "
&"FailedCount with sum as FailedCount, "
&"PartiallySucceededCount with sum as PartiallySucceededCount)) "
&"/compute( "
&"SucceededCount mul 100.0 div TotalCount as PassRate, "
&"FailedCount mul 100.0 div TotalCount as FailRate, "
&"PartiallySucceededCount mul 100.0 div TotalCount as PartiallySuccessfulRate) "
&"&$orderby=CompletedOn/Date asc "
,null, [Implementation="2.0",OmitValues = ODataOmitValues.Nulls,ODataVersion = 4])
in
Source
In my case I am using a Flat query as below:
https://analytics.dev.azure.com/{OrgName}/{ProjectName}/_odata/v3.0-preview/PipelineRuns?$apply=filter(CompletedDate ge 2023-06-01Z)
This will pull all Pipelines and the user will select the Pipeline that they wanted to see the Pass Rate Trend. I think using the named pipeline in the $apply=filter of the example code above is the same as allowing users to select the Pipeline from a dropdown.
As I am new to OData and PBI so looking for help/guidance to help me achieve the same result specific to getting the aggregated output based on Group By from a Flat Query.
Please suggest what shall be done to get: TotalCount, SucceededCount, FailedCount, PartiallySucceededCount ?
Thanks,
Prabhat
User | Count |
---|---|
11 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
9 | |
7 |