Forum Discussion

Pingu's avatar
Pingu
Regular Visitor
8 years ago
Solved

Keep only first value rest put 0

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

    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

Pingu's avatar
Pingu
Regular Visitor
8 years ago

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.

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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