Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
erdvige
Frequent Visitor

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 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.

 

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

View solution in original post

5 REPLIES 5
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

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.

@erdvige

 

What is your formula for VALUE column?

 

May be I can adjust it directly?


Regards
Zubair

Please try my custom visuals

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

 

@erdvige

 

1052.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.