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 sloppy and have deleted some of the parent work items without updating any children.  Which, of course, causes the PATH function to throw an error.

 

So I threw in a check to make sure that the parent actually exists and it will determine true or false correctly (shown, filtered for orphans only).

 

When I un-remark the IF statement above, PATH still returns an error even for determine path for only when parent does exist.

 

Obviously tried an IF statement, but also SWITCH and a very convoluted COALESCE all with the same results.  Have spent a couple hours Googling, looked at posts here, Reddit and Stack Overflow and while finding similar posts, nothing to explain this why the error still happens.

 

I have seen posts for using Power Query instead but would prefer a DAX solution because M code is not my friend.  But if I have to go that route, I will...

Was just curious if anyone could explain why.  Thank you!  ğŸ™‚



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.

     

8 Replies

  • 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!