IF and PATH in Calculated Column

[littlemojopuppy]

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

[lbendlin · Accepted solution]

Here is a much faster version

[littlemojopuppy · Accepted solution]

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!

[lbendlin]

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

Not sure if you can provide sample data?  

[littlemojopuppy]

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]

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]

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

[lbendlin · Accepted solution]

Here is a much faster version

[littlemojopuppy · Accepted solution]

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 · Accepted solution]

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.

[littlemojopuppy]

@lbendlin 

Me likey!!!  😍

I'm wondering if the faster Power Query version would work with keeping all the other columns in place.  Power Query is not my favorite...at times I find it very confusing (and frustrating).  Will try it out Monday and see what I can do with it.  If I find myself befuddled, I'll go with the brute force method.  It's only 6k records so it shouldn't be terrible I hope.

Thank you for looking at this!  I appreciate you and your help!  😊