Forum Discussion
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.
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)
8 Replies
LasseL Could you please post the sample data in copiable format, also your expected output.
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 Consume ActiveOperation LP026221 40000 0 0 LP026221 50000 0 0 LP026221 70000 0 0 LP026221 80000 0 0 LP026221 100000 0 0 LP026221 110000 4900 1 LP026221 130000 140 0 LP026221 150000 120 0 LP026221 160000 700 0 LP026221 180000 525 0 LP026222 40000 0 0 LP026222 50000 0 0 LP026222 70000 0 0 LP026222 80000 144 1 LP026222 100000 240 0 LP026222 110000 2016 0 LP026222 130000 576 0 LP026222 150000 120 0 LP026222 160000 288 0 LP026222 180000 216 0 Regarding type of attributes; Prod_ Order No_ is a text string, the rest are numeric values.
