Forum Discussion

LasseL's avatar
LasseL
Helper I
7 years ago
Solved

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; I...
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    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)

     

PattemManohar's avatar
PattemManohar
Community Champion
7 years ago

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)

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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. 

Create IndexFilter out zeros

Than you can remove duplicates of the products.

Remove duplicates

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

Table with rows that "matter".

 

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

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

 Insert Step After

 

 

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

Final column

Comparing the results

Cheers,

Pedro