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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pingu
Regular Visitor

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

 

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

10.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
Phil_Seamark
Employee
Employee

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?

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

 

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

10.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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