Looping a working DAX function for a calculated column

Question

I have a function that will look at a null field in column "Project Commit Cap" and lookup the value with the same ID and the next earlier date and give column "Project Commit Cap NoNulls" that value. DAX does not do it recursively so if there are 2 null values in a row I get a blank. In another language, a simple while loop (while value is null do: XYZ) would solve this, but DAX does not support For/While loops?

How can I get the working DAX function to apply the filled in values and keep running until there are no more nulls?

Optionally how can I brute force it to run X number of times (maybe 10)? it wouldn't be a pretty solution but will get the job done

Example table below is filtered to show only one 2 IDs, records of different IDs are between the records shown here so I cant do an excel-like function "take value from the row below"

What it does now

Here is the DAX function that will fill in the previous value (as shown in the pic above):

ProjectCommitCap_NoNulls =
IF (
    ISBLANK ( 'Project Story Data'[Projected Commitments Cap] ),
    LOOKUPVALUE (
        'Project Story Data'[Projected Commitments Cap],
        'Project Story Data'[ID], 'Project Story Data'[ID],
        'Project Story Data'[Date of Data], CALCULATE (
            MAX ( 'Project Story Data'[Date of Data] ),
            FILTER (
                'Project Story Data',
                'Project Story Data'[ID] = EARLIER ( 'Project Story Data'[ID] )
                    && 'Project Story Data'[Date of Data]
                        < EARLIER ( 'Project Story Data'[Date of Data] )
            )
        )
    ),
    'Project Story Data'[Projected Commitments Cap]
)

I need to look like this (all values in NoNulls to be filled in):

What I want it to look like

If you could leverage Power Query/M instead of DAX, it has a 'Fill Down' transform that does exactly what you want. It would be much simpler to do that. — greggyb, Sep 04 '19 at 15:53
Hey greggyb, unfortunately the wanted value will almost never be directly above or below the row that needs filling-in — Mathias W, Sep 04 '19 at 16:47
Wouldn't it be if you first sort by [ID] and [Date of Data]? Tables in M can have explicit ordering, unlike those in the Tabular engine. — greggyb, Sep 04 '19 at 16:50

score 1 · Answer 1 · answered Sep 04 '19 at 15:57

DAX doesn't really do loops at all but I think you can modify your formula to find the last non-blank date instead of just the previous date.

ProjectCommitCap_NoNulls =
IF (
    ISBLANK ( 'Project Story Data'[Projected Commitments Cap] ),
    LOOKUPVALUE (
        'Project Story Data'[Projected Commitments Cap],
        'Project Story Data'[ID], 'Project Story Data'[ID],
        'Project Story Data'[Date of Data], CALCULATE (
            MAX ( 'Project Story Data'[Date of Data] ),
            FILTER (
                'Project Story Data',
                'Project Story Data'[ID] = EARLIER ( 'Project Story Data'[ID] )
                    && NOT ( ISBLANK ( 'Project Story Data'[Projected Commitments Cap] ) )
                    && 'Project Story Data'[Date of Data]
                        < EARLIER ( 'Project Story Data'[Date of Data] )
            )
        )
    ),
    'Project Story Data'[Projected Commitments Cap]
)

As @greggyb points out though, using the 'Fill Down' transform in the query editor is a simpler solution.

Staying with DAX, you may find this Q&A useful as well: Return top value ordered by another column

Thanks Alexis! I don't know why I didn't think to add another && not ISBlank — Mathias W, Sep 04 '19 at 16:48

score 1 · Accepted Answer · edited Sep 05 '19 at 08:24

'Project Story Data'[ProjectCommitCap_NoNulls] =
IF (
    ISBLANK ( 'Project Story Data'[Projected Commitments Cap] ),
    VAR CurrentRowDate = 'Project Story Data'[Date of Data] // Table expression to find the most recent prior row for same [ID] that
    // has non-null [Project Commitment Cap]
    VAR PriorNonNullRow =
        CALCULATETABLE (
            // TOPN returns one row, here sorted by the [Date of Data]
            TOPN (
                1,
                'Project Story Data',
                'Project Story Data'[Date of Data], DESC
            ),
            // ALLEXCEPT clears all context, except for [ID], so the TOPN will
            // evaluate 'Project Story Data' for all rows with the same [ID]
            ALLEXCEPT (
                'Project Story Data',
                'Project Story Data'[ID]
            ),
            // Restrict it to only the [Date of Data] values less than current row
            'Project Story Data'[Date of Data] < CurrentRowDate,
            // And restrict it to just rows with non-null values
            NOT ( ISBLANK ( 'Project Story Data'[Projected Commitments Cap] ) )
        )
    RETURN
        // Using the table above as our filter context, find the MAX (or MIN, or VALUES -
        // it doesn't matter - just need to coerce the field to a scalar) of 
        // [Projected Commitments Cap]
        CALCULATE (
            MAX ( 'Project Story Data'[Projected Commitments Cap] ),
            PriorNonNullRow
        ),
    // This is arg3 of IF
    'Project Story Data'[Projected Commitments Cap]
)

Using this approach should be more storage-engine-friendly, and therefore more cache-able than a LOOKUPVALUE.

Thanks greggyb! works perfectly. Since its less intensive i'll be using this method! — Mathias W, Sep 04 '19 at 16:59

Looping a working DAX function for a calculated column

2 Answers2