Forum Discussion

lawena's avatar
lawena
Regular Visitor
2 years ago
Solved

Calculated column with a value from a previous row

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: ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

     

bhanu_gautam's avatar
bhanu_gautam
Super User
2 years ago

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's avatar
lawena
Regular Visitor
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      lawena
      Regular Visitor
      2 years ago

      Brilliant - this works on my data, thank you Anonymous