Forum Discussion
Next Non Blank Value from Calculated Column
Hi all,
First of all, what I'm trying to achieve will not be possible with a fill down method as all operations are being done to calculated columns. So I'd need a DAX calculated column to solve this issue.
I have the following table that contains an Index that defines a certain contact (we are looking at data for contact # 3 below), IndexDateRank, which orders the entries per contact by date, and Stage, that may or may not have a value for each entry. I want to be able to generate an Outcome column where it calculates what's the next non blank value, as seen below:
| Index | IndexDateRank | Stage | Outcome |
| 3 | 1 | 6 | 3 |
| 3 | 2 | ||
| 3 | 3 | ||
| 3 | 4 | ||
| 3 | 5 | ||
| 3 | 6 | 3 | 1 |
| 3 | 7 | ||
| 3 | 8 | ||
| 3 | 9 | ||
| 3 | 10 | ||
| 3 | 11 | ||
| 3 | 12 | ||
| 3 | 13 | ||
| 3 | 14 | 1 | 1 |
| 3 | 15 | ||
| 3 | 16 | ||
| 3 | 17 | ||
| 3 | 18 | ||
| 3 | 19 | ||
| 3 | 20 | 1 | 5 |
| 3 | 21 | ||
| 3 | 22 | ||
| 3 | 23 | ||
| 3 | 24 | 5 | 5 |
| 3 | 25 |
Does anyone have a solution on how to generate the Outcome column?
Thanks in advance,
Antonio
Hi , Anonymous
Try calculated column as below:
Column = VAR index_ = CALCULATE ( MIN ( [IndexDateRank] ), FILTER ( 'Table', [IndexDateRank] > EARLIER ( [IndexDateRank] ) && [Stage] <> BLANK () ) ) RETURN IF ( [Stage] = BLANK (), BLANK (), CALCULATE ( MIN ( 'Table'[Stage] ), FILTER ( 'Table', [IndexDateRank] = index_ ) ) )The column result will show as below:
Please check attached pbix file for more details.
Best Regards,
Community Support Team _ Eason
2 Replies
Hi , Anonymous
Try calculated column as below:
Column = VAR index_ = CALCULATE ( MIN ( [IndexDateRank] ), FILTER ( 'Table', [IndexDateRank] > EARLIER ( [IndexDateRank] ) && [Stage] <> BLANK () ) ) RETURN IF ( [Stage] = BLANK (), BLANK (), CALCULATE ( MIN ( 'Table'[Stage] ), FILTER ( 'Table', [IndexDateRank] = index_ ) ) )The column result will show as below:
Please check attached pbix file for more details.
Best Regards,
Community Support Team _ Eason