Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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...
Solved! Go to 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
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?
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] )
Regards,
Xiaoxin Sheng
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |