Forum Discussion

littlemojopuppy's avatar
littlemojopuppy
Community Champion
2 years ago
Solved

IF and PATH in Calculated Column

Hi.  I'm stumped on something that should be fairly easy...   I have Azure DevOps data and trying to build the hierarchy of Epic / Feature / Story / Task in a calculated column.  Except users are...
Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    2 years ago

    Hi lbendlin 

     

    Tried the idea of joining the table onto itself, but then I couldn't get the output correct.  Probably due to operator error on my part.

     

    But I did come up with another solution to this...  First, took the field of valid WorkItemIDs and turned it into a list.  Then added a custom column like this

     

    Thank you for your help!

     

  • littlemojopuppy's avatar
    littlemojopuppy
    2 years ago

    Ok.  I've never been a fan of adding columns for a calculation and deleting the original.  Creates lots of additional steps that really don't need to be there.  So I reworked the solution I provided earlier.  Now it replaces the value if the List.Contains finds the ParentWorkItemID value in a list of Valid WorkItemIDs.  Thing about this solution is that it was painfully slow.  More than 15 minutes to import about 6.2k records.

     

    Modified slightly to add a step to List.Buffer the valid WorkItemIDs in memory and then perform the List.Contains step.  Went from 15ish minutes to seconds.

     

lbendlin's avatar
lbendlin
Super User
2 years ago

Hey, no dissing of COALESCE!  It might well be your ticket. 

 

Not sure if you can provide sample data?  

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    2 years ago

    Hi lbendlin 

    I wasn't dissing COALESCE...I was dissing my implementation of COALESCE!  ğŸ˜‰

    I can export some data including the problem children.  Will try to anonymize as best I can.  Will be in a following post (have to log into work laptop).

    • littlemojopuppy's avatar
      littlemojopuppy
      Community Champion
      2 years ago

      HI lbendlin 

      Link for DevOps data.  Removed all the columns not relevant to this because I theoretically could be fired for sharing company data, but a bunch of numbers with no context minimizes that, I hope.

      • The orange fields are the fields used in the PATH function: WorkItemID and ParentWorkItemID.
      • The green field is the one I'm having trouble with.  Current DAX is below.

       

      WorkItemPath = 
VAR IsOrphaned =
    ISEMPTY(
        FILTER(
            ALL('Work Items'[WorkItemId]),
            'Work Items'[WorkItemId] = 'Work Items'[ParentWorkItemId]
        )
    )
RETURN

//IF(
//    IsOrphaned = TRUE(),
//    BLANK(),
//    PATH (
//        'Work Items'[WorkItemId],
//        'Work Items'[ParentWorkItemId]
//    )
//)
IsOrphaned

       

      I would have expected the IF statement to return blank for anything orphaned/not having a parent and the path for anything with a parent.  I've verified that the logic to check for if something is orphaned is working correctly by reviewing the 16 work items reported as orphans.  The problem seems to be with the IF statement, like it's evaluating both the true and false branches before evaluating the IF condition.

      Would appreciate any help you could give!  Thank you!

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

        Well, there goes my original idea

         

         

        Anyway, here's a brute force method (awfully slow at the moment) that "fixes"  your data quality issues.

        IsOrphaned is basically equivalent to PathLength=1