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

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