Dealing with Blank Values By Using NEXT IN COLUMN

[v-merpet]

IF I were looking for a Pseudo Code description of my problem it would be this: 

             --    IF(IsBlank(Value1),Lookup(Value2) RETURN First NON BLANK (Value1)Else(Lookup(Value2) NEXT Row IN Column

Sample Data: 

Change Date          Status          Iteration        WorkID 

1/1/2020                NEW                NULL            12345

1/2/2020                TRIAGE            NULL            12345

1/2/2020                ACTIVE             W1               12345

1/3/2020                REVIEW            W2               12345

1/4/2020                DONE               W2               12345

1/2/2020               NEW               NULL              54321

1/3/2020               ACTIVE              W1               54321

1/4/2020               DONE                W1              54321

I need a DAX Query that says IF the Iteration value is NULL then look to the next value in that column based on the WorkID.

AND

If that Next Value ISBLANK Lookup(Again) Next Value..

So that the data fills in the NULL values like this:

Change Date          Status          Iteration        WorkID 

1/1/2020                NEW                W1              12345           Here STATUS lookup the Work ID by sequence two column values to 

1/2/2020                TRIAGE            W1              12345                    find a valid iteration. SO IF(ISBLANK Lookup AND IF(ISBLANK)

1/2/2020                ACTIVE             W1               12345

1/3/2020                REVIEW            W2               12345

1/4/2020                DONE               W2               12345

1/2/2020               NEW                  W1              54321

1/3/2020               ACTIVE              W1               54321

1/4/2020               DONE                W1              54321

Hope this example is clear but if more details are needed please advise. Any guidance you can offer would be greatly appreciated.

[v-lid-msft · Accepted solution]

Hi @v-merpet ,

We can create a calculated column using following DAX to meet your requirement:

Iteraction_New =
IF (
    ISBLANK ( 'Table'[Iteraction] )
        || 'Table'[Iteraction] = "",
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Iteraction], TRUE () ),
        FILTER (
            'Table',
            'Table'[WorkID] = EARLIER ( 'Table'[WorkID] )
                && 'Table'[Change Date] >= EARLIER ( 'Table'[Change Date] )
                && 'Table'[Iteraction] <> ""
        )
    ),
    [Iteraction]
)

Best regards,

[artemus]

This is more easily accomplished in power query, if you don't mind changing the dataset to include these values.

Simply go to edit query, select the iteration column, and choose fill up.

If you need some help with this, just ping me on teams.