Forum Discussion

erdvige's avatar
erdvige
Frequent Visitor
8 years ago
Solved

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

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...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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]
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
    erdvige
    Frequent Visitor
    8 years ago

    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's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      erdvige

       

      What is your formula for VALUE column?

       

      May be I can adjust it directly?

      • erdvige's avatar
        erdvige
        Frequent Visitor
        8 years ago

        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]
    )