Search code examples
azure-devopspowerbidax

How to count direct and indirect test cases for Features, Requirements, and Change Requests in Power BI using DAX?

I have below work item hierarchy in Azure DevOps

enter image description here

I have imported the data to Power BI

enter image description here

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

enter image description here

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?

enter image description here

Solution

  • 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"
    )
  )