admin 管理员组

文章数量: 1184232

I have below work item hierarchy in Azure DevOps

I have imported the data to Power BI

I am trying to use Power BI to visualize the data as shown below

I successfully obtained the Change Request Count and Requirement Count, but I'm unable to calculate the Test Case Count. As shown above, Features 1 and 2 have a Test Case Count of ZERO!

Here is my DAX code

 Total Number of Testcases = 
 // Step 1: Directly linked test cases
 VAR DirectTestCases = 
 CALCULATE(
    COUNTROWS(SoftwareFacoryData),
    FILTER(
        SoftwareFacoryData,
        SoftwareFacoryData[Parent Work Item Id] = EARLIER(SoftwareFacoryData[Work Item ID]) &&
        SoftwareFacoryData[Work Item Type] = "Test Case" &&
        SoftwareFacoryData[Is Current] = TRUE()
    )
)

 // Step 2: Find all indirectly linked work items (Requirements, Change Requests, etc.)
 VAR IndirectLinks = 
 FILTER(
    SoftwareFacoryData,
    SoftwareFacoryData[Parent Work Item Id] = EARLIER(SoftwareFacoryData[Work Item ID]) &&
    SoftwareFacoryData[Is Current] = TRUE()
)

    // Step 3: Gather test cases indirectly linked through intermediate items
    VAR IndirectTestCases =
    CALCULATE(
      COUNTROWS(SoftwareFacoryData),
    FILTER(
        SoftwareFacoryData,
        SoftwareFacoryData[Work Item Type] = "Test Case" &&
        SoftwareFacoryData[Is Current] = TRUE() &&
        SoftwareFacoryData[Parent Work Item Id] IN 
        SELECTCOLUMNS(
            IndirectLinks,
            "WorkItemID", SoftwareFacoryData[Work Item ID]
        )
    )
)

   // Step 4: Total test cases (Direct + Indirect)
    VAR TotalTestCases = DirectTestCases + IndirectTestCases

    // Step 5: Return total count, ensuring 0 for BLANK
 RETURN
 IF(
   SoftwareFacoryData[Work Item Type] IN {"Feature", "Change Request", "Requirement"} &&
  SoftwareFacoryData[Is Current] = TRUE(),
  COALESCE(TotalTestCases, 0),
  0
)

I am not able to solve this :(, I am suspecting that Feature do not have any Parent work item Id and may be its causing the issue.

Basically I want DAX code to go through hierarchy and count the testcases for every work item

Based on Sam Nseir answer, I have created Item Path which gave me below data , Now I want to know how to get the Test Case Count using this PATH?

This is my data source (Analytical view from Azure DevOps)

I have below work item hierarchy in Azure DevOps

I have imported the data to Power BI

I am trying to use Power BI to visualize the data as shown below

I successfully obtained the Change Request Count and Requirement Count, but I'm unable to calculate the Test Case Count. As shown above, Features 1 and 2 have a Test Case Count of ZERO!

Here is my DAX code

 Total Number of Testcases = 
 // Step 1: Directly linked test cases
 VAR DirectTestCases = 
 CALCULATE(
    COUNTROWS(SoftwareFacoryData),
    FILTER(
        SoftwareFacoryData,
        SoftwareFacoryData[Parent Work Item Id] = EARLIER(SoftwareFacoryData[Work Item ID]) &&
        SoftwareFacoryData[Work Item Type] = "Test Case" &&
        SoftwareFacoryData[Is Current] = TRUE()
    )
)

 // Step 2: Find all indirectly linked work items (Requirements, Change Requests, etc.)
 VAR IndirectLinks = 
 FILTER(
    SoftwareFacoryData,
    SoftwareFacoryData[Parent Work Item Id] = EARLIER(SoftwareFacoryData[Work Item ID]) &&
    SoftwareFacoryData[Is Current] = TRUE()
)

    // Step 3: Gather test cases indirectly linked through intermediate items
    VAR IndirectTestCases =
    CALCULATE(
      COUNTROWS(SoftwareFacoryData),
    FILTER(
        SoftwareFacoryData,
        SoftwareFacoryData[Work Item Type] = "Test Case" &&
        SoftwareFacoryData[Is Current] = TRUE() &&
        SoftwareFacoryData[Parent Work Item Id] IN 
        SELECTCOLUMNS(
            IndirectLinks,
            "WorkItemID", SoftwareFacoryData[Work Item ID]
        )
    )
)

   // Step 4: Total test cases (Direct + Indirect)
    VAR TotalTestCases = DirectTestCases + IndirectTestCases

    // Step 5: Return total count, ensuring 0 for BLANK
 RETURN
 IF(
   SoftwareFacoryData[Work Item Type] IN {"Feature", "Change Request", "Requirement"} &&
  SoftwareFacoryData[Is Current] = TRUE(),
  COALESCE(TotalTestCases, 0),
  0
)

I am not able to solve this :(, I am suspecting that Feature do not have any Parent work item Id and may be its causing the issue.

Basically I want DAX code to go through hierarchy and count the testcases for every work item

Based on Sam Nseir answer, I have created Item Path which gave me below data , Now I want to know how to get the Test Case Count using this PATH?

This is my data source (Analytical view from Azure DevOps)

  • azure-devops
  • powerbi
  • dax
Share Improve this question edited Jan 28 at 4:26 kudlatiger asked Jan 26 at 6:38 kudlatigerkudlatiger 3,25812 gold badges54 silver badges110 bronze badges
Add a comment  | 

1 Answer 1

Reset to default 1

Check out Parent and Child functions.

Create a new calculated column with:

Item Path = PATH(SoftwareFactoryData[Work Item Id], SoftwareFactoryData[Parent Work Item Id])

Then in your calculations, you would use it like so:

Test Cases # =
  var thisId = SoftwareFactoryData[Work Item Id]
  return CALCULATE(
    COUNTROWS(SoftwareFactoryData),
    FILTER(
      SoftwareFactoryData,
      PATHCONTAINS(SoftwareFactoryData[Item Path], thisId) && 
      SoftwareFactoryData[Work Item Type] = "Test Case"
    )
  )

本文标签：

Error[2]: Invalid argument supplied for foreach(), File: /www/wwwroot/roclinux.cn/tmp/view_template_quzhiwa_htm_read.htm, Line: 58
File: /www/wwwroot/roclinux.cn/tmp/route_read.php, Line: 205, include(/www/wwwroot/roclinux.cn/tmp/view_template_quzhiwa_htm_read.htm)
File: /www/wwwroot/roclinux.cn/tmp/index.inc.php, Line: 129, include(/www/wwwroot/roclinux.cn/tmp/route_read.php)
File: /www/wwwroot/roclinux.cn/index.php, Line: 29, include(/www/wwwroot/roclinux.cn/tmp/index.inc.php)

版权声明：本文标题：azure devops - How to count direct and indirect test cases for Features, Requirements, and Change Requests in Power BI using DAX 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.roclinux.cn/p/1738326305a1963967.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。