cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

 

IndexIndexDateRankStageOutcome
3163
32  
33  
34  
35  
3631
37  
38  
39  
310  
311  
312  
313  
31411
315  
316  
317  
318  
319  
32015
321  
322  
323  
32455
325  

         

Does anyone have a solution on how to generate the Outcome column?

 

Thanks in advance,

Antonio

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
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:

25.png

 

Please check attached pbix file for more details.

Best Regards,
Community Support Team _ Eason

 

View solution in original post

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

25.png

 

Please check attached pbix file for more details.

Best Regards,
Community Support Team _ Eason

 

View solution in original post

Highlighted

Hi Eason,

 

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

Thank you very much.

 

Best regards,

Antonio

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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