Dealing with Blank Values By Using NEXT IN COLUMN

Question

IF I were looking for a Pseudo Code description of my problem it would be this:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; -- &nbsp;&nbsp; IF(IsBlank(Value1),Lookup(Value2) RETURN First NON BLANK (Value1)Else(Lookup(Value2) NEXT Row IN ColumnSample Data:&nbsp;&nbsp;Change Date &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Status &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Iteration &nbsp; &nbsp; &nbsp;&nbsp; WorkID&nbsp;1/1/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; NEW &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 123451/2/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; TRIAGE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 123451/2/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; ACTIVE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; W1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123451/3/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; REVIEW &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; W2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123451/4/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; DONE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; W2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123451/2/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NEW &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 543211/3/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ACTIVE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; W1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 543211/4/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DONE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; W1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 54321&nbsp;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.ANDIf that Next Value ISBLANK Lookup(Again) Next Value..&nbsp;So that the data fills in the NULL values like this:Change Date &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Status &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Iteration &nbsp; &nbsp; &nbsp;&nbsp; WorkID&nbsp;1/1/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; NEW &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; W1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 12345 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Here STATUS lookup the Work ID by sequence two column values to&nbsp;1/2/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; TRIAGE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; W1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 12345 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; find a valid iteration. SO IF(ISBLANK Lookup AND IF(ISBLANK)1/2/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; ACTIVE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; W1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123451/3/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; REVIEW &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; W2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123451/4/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; DONE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; W2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123451/2/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NEW &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; W1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 543211/3/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ACTIVE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; W1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 543211/4/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DONE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; W1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 54321&nbsp;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 Answer

Hi&nbsp;v-merpet&nbsp;,
&nbsp;
We can create a calculated column using following DAX to meet your requirement:
&nbsp;
Iteraction_New =
IF (
    ISBLANK ( 'Table'[Iteraction] )
        || 'Table'[Iteraction] = "",
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Iteraction], TRUE () ),
        FILTER (
            'Table',
            'Table'[WorkID] = EARLIER ( 'Table'[WorkID] )
                &amp;&amp; 'Table'[Change Date] &gt;= EARLIER ( 'Table'[Change Date] )
                &amp;&amp; 'Table'[Iteraction] &lt;&gt; ""
        )
    ),
    [Iteraction]
)
&nbsp;

Best regards,
&nbsp;

Forum Discussion

Dealing with Blank Values By Using NEXT IN COLUMN

2 Replies

Related content

Dealing with missing Values

How to deal with null value and blank value in power Query ?

Splitting Column values

Matching Column Values

dealing with a list of records (and null values)