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

 

  • 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

3 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

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