Using last non-zero value in the same column to populate a specific row's value

[erdvige]

Sorry if this has come up before but new user and trying to get my head around some of the functions in PowerBI. I have a series of ~ 20 million sample rows for which I am attributing values based on the sample results in the table. I am using the function "Column = LOOKUPVALUE('Result Scores'[Score],'Result Scores'[Code],[Result])" to cross reference the results to the corresponding value. The data looks something like this:

Sample Number          Date    Result  Value

Sample1          1/1/2000          FOU    3

Sample2          1/2/2000          KNA   3

Sample3          1/3/2000          QSB    2

Sample4          1/4/2000          LSD    3

Sample5          1/5/2000          ICN     0

Sample6          1/6/2000          ICN     0

Sample7          1/7/2000          ICN     0

Sample8          1/8/2000          ICN     0

Sample9          1/9/2000          TGS     3

Sample10        1/10/2000        MHG   2

Sample11        1/11/2000        MQV   2

Sample12        1/12/2000        NOE    3

Sample13        1/13/2000        KRW   1

Sample14        1/14/2000        QXV   3

Sample15        1/15/2000        FIB      1

As you can see, result value "ICN" pulls a 0 value. There are multiple such results that could pull a 0 instead of value 1-3, and they can be sequential as they appear in the above. Since the ICN results follow result LSD, I want each of the ICN results to carry over the value 3, not 0. This would be true whether it was a 1, 2 or 3. The results with a 0 value should carry the value of the previous sample indefinitely until a sample with a non-zero value comes up. I know I can use IF with LOOKUPVALUE to reference a previous row's result, but I am not sure how to continuously use that result until a non-zero value is returned.

[Zubair_Muhammad · Accepted solution]

Hi @erdvige

Try adding this calculated Column

New values =
VAR LastNonZeroDate =
    MAXX (
        FILTER ( Table1, Table1[Date] < EARLIER ( Table1[Date] ) && Table1[Value] <> 0 ),
        Table1[Date]
    )
RETURN
    IF (
        Table1[Value] = 0,
        CALCULATE (
            VALUES ( Table1[Value] ),
            FILTER ( ALL ( Table1 ), Table1[Date] = LastNonZeroDate )
        ),
        Table1[Value]
    )

[erdvige]

So this is working on a sample set of data. But when I expand it into the full list with 20 mil+ records I'm forced to have two new columns: the one you proposed and one which concatenates a unique identifier for each row combined with the date/time value, since the date/time has nonunique values. But in doing this I'm now overtaxing my system. The solution works so long as it's not too much data. I may need to find a different approach.

[Zubair_Muhammad]

@erdvige

What is your formula for VALUE column?

May be I can adjust it directly?

[erdvige]

So the two new columns I created:

Prelim Score	ID&Date
1	10164842250
1	10164842251
1	10164842252
1	10164842253
2	10164842254
1	10164842255
1	10164842256
3	10164842257
3	10164842258
3	10164842259
0	10164842260
0	10164842261
0	10164842262
0	10164842263
1	10164842264
1	10164842265
1	10165542257
1	10165542258
1	10165542259
2	10165542260
2	10165542261
2	10165542262
1	10165542263
1	10165542264
1	10165542265
1	10165542266
0	10165542267
0	10165542268
3	10165542269
0	10165542270
1	10165542271
1	10165542272

The ID&Date column concatenates the ID of the sample series with the numerical value of the date on which it was done. All date values are whole numbers, the data is sorted primarily by ID and then by date. From there I modified your code to refer to these two columns:

New values =
VAR LastNonZeroDate =
    MAXX (
        FILTER ( 'History Sheets', 'History Sheets'[ID&Date] < EARLIER ( 'History Sheets'[ID&Date] ) && 'History Sheets'[Prelim Score] <> 0 ),
        'History Sheets'[ID&Date]
    )
RETURN
    IF (
        'History Sheets'[Prelim Score] = 0,
        CALCULATE (
            VALUES ( 'History Sheets'[Prelim Score] ),
            FILTER ( ALL ( 'History Sheets' ), 'History Sheets'[ID&Date] = LastNonZeroDate )
        ),
        'History Sheets'[Prelim Score]
    )

[Zubair_Muhammad]

@erdvige