Forum Discussion
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:
| 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.
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
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.
4 Replies
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()
)Thanks bhanu_gautam
I've tried a similar query using MAXXThe 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 | |
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.
