Calculated column with a value from a previous row

[lawena]

I want to create a column that holds the value of the ID for a Feature's "parent" Objective

My table looks like this, all subsequent Features under the Objective are children of the Objective:

| Index | ID  | Work Item type |

|-------|-----|----------------|

| 1     | 7   | Objective      |

| 2     | 5   | Feature        |

| 3     | 4   | Feature        |

| 4     | 2   | Objective      |

| 5     | 8   | Feature        |

| 6     | 1   | Objective      |

The result I want is this:

| Index | ID  | Work Item type | PreviousObjectiveID |

|-------|-----|----------------|---------------------|

| 1     | 7   | Objective      |                     |

| 2     | 5   | Feature        | 7                   |

| 3     | 4   | Feature        | 7                   |

| 4     | 2   | Objective      |                     |

| 5     | 8   | Feature        | 2                   |

| 6     | 1   | Objective      |                     |

I've tried a few queries, with and without an index column, however have failed to get it to work.

PreviousObjectiveID =

VAR PreviousObjectiveID =

    MAXX(

        FILTER(

            ClosedFeaturesByObjective,

            ClosedFeaturesByObjective[Index] >= EARLIER(ClosedFeaturesByObjective[Index]) &&

            ClosedFeaturesByObjective[Work Item type] = "Objective"

        ),

        ClosedFeaturesByObjective[ID]

    )

RETURN

    IF(

        ClosedFeaturesByObjective[Work Item type] = "Feature",

        PreviousObjectiveID,

        BLANK()

    )

+++++++++++

ObjectiveID = 

VAR CurrentRowID = ClosedFeaturesByObjective[ID]

VAR PreviousObjectiveID =

    MAXX(

        FILTER(

            ClosedFeaturesByObjective,

            ClosedFeaturesByObjective[ID] < CurrentRowID &&

            ClosedFeaturesByObjective[Work Item type] = "Objective"

        ),

        ClosedFeaturesByObjective[ID]

    )

RETURN

    IF(

        ClosedFeaturesByObjective[Work Item type] = "Feature",

        PreviousObjectiveID,

        BLANK()

    )

I'm quite new to DAX, so help would be much appreciated.
Thanks

[bhanu_gautam]

@lawena , You can try creating a new calculated column using below DAX

DAX
PreviousObjectiveID =
VAR CurrentIndex = ClosedFeaturesByObjective[Index]
VAR PreviousObjectiveID =
MAXX(
FILTER(
ClosedFeaturesByObjective,
ClosedFeaturesByObjective[Index] < CurrentIndex &&
ClosedFeaturesByObjective[Work Item type] = "Objective"
),
ClosedFeaturesByObjective[ID]
)
RETURN
IF(
ClosedFeaturesByObjective[Work Item type] = "Feature",
PreviousObjectiveID,
BLANK()
)

[lawena]

Thanks @bhanu_gautam 

I've tried a similar query using MAXX

The result gives the first objectiveID to all the Features, similar to this:

| Index | ID  | Work Item type | PreviousObjectiveID |

|-------|-----|----------------|---------------------|

| 1     | 7   | Objective      |                     |

| 2     | 5   | Feature        | 7                   |

| 3     | 4   | Feature        | 7                   |

| 4     | 2   | Objective      |                     |

| 5     | 8   | Feature        | 7                   |

| 6     | 1   | Objective      |                     |

[Anonymous · Accepted solution]

Hi,

Thanks for the solution bhanu_gautam offered and i want to offer some more information for user to refer to.

hello @lawena , you can try the following calculated column.

Column =
VAR _filterindex =
    MAXX (
        FILTER (
            'ClosedFeaturesByObjective',
            [Work Item type] = "Objective"
                && [Index] <= EARLIER ( 'ClosedFeaturesByObjective'[Index] )
        ),
        [Index]
    )
VAR _id =
    LOOKUPVALUE ( 'ClosedFeaturesByObjective'[ID], [Index], _filterindex )
RETURN
    IF ( [Work Item type] = "Feature", _id )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lawena]

Brilliant - this works on my data, thank you @Anonymous