cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
LasseL Frequent Visitor
Frequent Visitor

Calculated Column looking up values in same table

Dear Experts,

 

This solution might be simple, but I am struggling a lot to work out how to make calculated column which is derived from values from same table.

 

The logic I am looking for is; IF Qty_ to consume = 0 in table where Prod_ Order No_ = Prod_Order No_ of actual row AND Line No_ < Line No of actual row AND Max Line No AND Qty_ to Consume of actual row >0 THEN 1 ELSE 0

 

I have tried something along with IF(CALCULATE(SUM(Qty_ to consume);FILTER(Prod Line;Prod_ Order No_=Prod_Order No_ && Line No_<Line No_ && Max(Line No_);IF(Qty_ to Consume>0);1;0)

 

I am pretty sure I am getting som syntax wrong, any ideas how to get around this?

 

I could solve it by creating a calculated table with values I am interested in and a key and relation and this way make a max and check, but it is not a pretty solution performance-wise.

 

Udklip.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculated Column looking up values in same table

@LasseL Thanks for confirming that !!

 

Here you go with the "ActiveOperation" column logic (You need to have an Index field added using "Power Query Editor" before creating the new column as below)

 

ActiveOperation = 
VAR _PrevTotal = CALCULATE(SUM(Test115Flag[QtyToConsume]),FILTER(ALL(Test114Rnk),Test115Flag[ProdOrderNo]=EARLIER(Test115Flag[ProdOrderNo]) && Test115Flag[Index] < EARLIER(Test115Flag[Index]))) 
VAR _PrevRowVal = LOOKUPVALUE(Test115Flag[QtyToConsume],Test106CummSum[Index],Test115Flag[Index]-1)
RETURN IF(_PrevTotal>0 && _PrevRowVal = 0 && _PrevTotal = Test115Flag[QtyToConsume],1,0)

image.png

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





8 REPLIES 8
Super User
Super User

Re: Calculated Column looking up values in same table

@LasseL Could you please post the sample data in copiable format, also your expected output.



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





LasseL Frequent Visitor
Frequent Visitor

Re: Calculated Column looking up values in same table

Hi there Pattem, thanks for your quick response.

 

Certainly, here you go, a sample in table form, last column is the expected output.

 

Prod_ Order No_Line No_Qty_ to ConsumeActiveOperation
LP0262214000000
LP0262215000000
LP0262217000000
LP0262218000000
LP02622110000000
LP02622111000049001
LP0262211300001400
LP0262211500001200
LP0262211600007000
LP0262211800005250
LP0262224000000
LP0262225000000
LP0262227000000
LP026222800001441
LP0262221000002400
LP02622211000020160
LP0262221300005760
LP0262221500001200
LP0262221600002880
LP0262221800002160
LasseL Frequent Visitor
Frequent Visitor

Re: Calculated Column looking up values in same table

Regarding type of attributes; Prod_ Order No_ is a text string, the rest are numeric values.

Super User
Super User

Re: Calculated Column looking up values in same table

@LasseL Just want to confirm - You want to flag "ActiveOperation" as 1 for first non-zero value within each Prod_Order_No group and others as 0. Is that correct ?



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





LasseL Frequent Visitor
Frequent Visitor

Re: Calculated Column looking up values in same table

That is very well formulated and correct, as a calculated column.

Super User
Super User

Re: Calculated Column looking up values in same table

@LasseL Thanks for confirming that !!

 

Here you go with the "ActiveOperation" column logic (You need to have an Index field added using "Power Query Editor" before creating the new column as below)

 

ActiveOperation = 
VAR _PrevTotal = CALCULATE(SUM(Test115Flag[QtyToConsume]),FILTER(ALL(Test114Rnk),Test115Flag[ProdOrderNo]=EARLIER(Test115Flag[ProdOrderNo]) && Test115Flag[Index] < EARLIER(Test115Flag[Index]))) 
VAR _PrevRowVal = LOOKUPVALUE(Test115Flag[QtyToConsume],Test106CummSum[Index],Test115Flag[Index]-1)
RETURN IF(_PrevTotal>0 && _PrevRowVal = 0 && _PrevTotal = Test115Flag[QtyToConsume],1,0)

image.png

 



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





LasseL Frequent Visitor
Frequent Visitor

Re: Calculated Column looking up values in same table

Great, I am struggling a bit getting the Index column to give the right order based on "Prod_orderNo" and LineNo, I'll continue to get that fixed and get back as soon as it is resolvedin order to test your calculation. Thanks.

Gravanita Regular Visitor
Regular Visitor

Re: Calculated Column looking up values in same table

If you're going to use the Power Query Editor you might as well do every thing there.

I imported the data with the final column in place. We can use it to check the solution.

 

After creating the index, you can filter out all the zero quantidy records. 

pic1.JPGCreate Indexpic2.JPGFilter out zeros

Than you can remove duplicates of the products.

pic3.JPGRemove duplicates

Your left with all the first rows where a product has quantity greater than 0.

pic4.JPGTable with rows that "matter".

 

Relate them with the table you had after creating the indexes.

To do this, insert a step "Insert Step After".

 pic5.JPGInsert Step After

 pic6.JPG

 

Set the "step value" to #"Added Index". Since the name has a space we have to use the quotes preceded by the pound symbol.

 

The next step is to add a new column. I named it "Active Operations 2".

pic7.JPGFinal column

pic8.JPGComparing the results

Cheers,

Pedro