Keep only first value rest put 0

[Pingu]

Hi,

I wonder how to get the following:

I have a column with a test Nr

Test Nr  Qty

t1          5

t1           5

t1           5

t1           5

t2           3

t2           3

I'd like a calculated column or measure that keep all lines with Test Nr but only keeps the 1st Qty value:

Test Nr  QtyClean

t1          5

t1           0

t1           0

t1           0

t2           3

t2           0

I tried EARLIER but I get error...

[Phil_Seamark]

Hi @Pingu

Do you have any other columns to order by?  As far as your sample data is concerned, the first three rows are identical.  Do you have another TimeStamp or ID column that can be used to identify the first (and not first) rows?

[Pingu]

I have:

Test Nr  Qty   DefType

t1          5          A

t1           5           P

t1           5           B

t1           5           T

t2           3           A

t2           3           B

I tried to create a new column:

Qty_simple = if(filter(Data;Data[test Nr]= EARLIER(Data[test Nr]));Qty;0)

but I get an error.

[v-shex-msft · Accepted solution]

Hi @Pingu,

I think you still need to add an index column to calculate through table, type column obviously not suitable use as index column.

You can enter to query editor to add index column, then use below formula to replace qty value.

Replaced =
IF (
    [Index]
        > CALCULATE (
            MIN ( Table1[Index] ),
            FILTER ( ALL ( Table1 ), Table1[Test Nr] = EARLIER ( Table1[Test Nr] ) )
        ),
    0,
    [Qty]
)

Regards,

Xiaoxin Sheng