cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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?

Antonio

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

Hi , @antoniogouveia

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 2
Highlighted
Community Support

Hi , @antoniogouveia

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

Highlighted
Frequent Visitor

Hi Eason,

Wow that's exactly it! You made it look so easy and elegant!

Thank you very much.

Best regards,

Antonio

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors