Forum Discussion

mb0307's avatar
mb0307
Responsive Resident
5 years ago

Identify cancelled and duplicate items

Hi,

 

I have a similar post (https://community.powerbi.com/t5/Desktop/Identify-canceled-items/m-p/1497079#M620152) but my requirement has changed so opening a new post.

 

Table below shows order details and in column MOVEMENT TYPE value 102 represents the cancelled order - CLICK TO DOWNLOAD DATA :

 

"Remove" should be added to a calculated column based on two criteria below:

  1. If MOVEMENT TYPE is 102 then find ONE EQUIVALENT RECORD from MOVEMENT TYPE 101 with the same PO NUMBER, ITEM, BATCH and POSTING QUANTITY then add "Remove" in the RESULT column.  Highlighted in yellow and shown with arrows.
  2. If DOC REF per ITEM is repeated in any other SEQUENCE greater than min SEQUENCE, then it should be "Remove" otherwise do not remove. Highlighted in blue.

 

Please note that there can be multiple rows with value102 per ITEM so it is important to identify based on above criteria 1.

 

Thanks

Need Help

3 Replies

  • mb0307's avatar
    mb0307
    Responsive Resident
    5 years ago

    Can anyone look into my request please?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      mb0307 
      For criteria 2, you could use following expression to filter:

      Column = IF([Sequence]<>CALCULATE(MIN('table'[Sequence]),ALLEXCEPT('table','table'[PO Number],'table'[PO Date],'table'[Item],'table'[Posting Date],'table'[Posting Quantity],'table'[Movement Type],'table'[Batch],'table'[DOC REF])),"Remove")

       

      But for criteria 1, I don't see the text description matches with the image. What do you mean by find ONE EQUIVALENT RECORD, and you the condition is if the type =102, but I see some type 101 rows highlighted in yellow. Can you maybe rephrase and clarify?

       

      Regards
      Paul

      • mb0307's avatar
        mb0307
        Responsive Resident
        5 years ago

        Anonymous  Thanks for your response.   Sorry I wasn't clear in the description. 

         

        for Criteria 1,  I want to cancel ONE EQUIVALENT RECORD from MOVEMENT TYPE 101.  Please see example below.  Each arrow from 102 is cancelling 101 with same PO NUMBER, ITEM, BATCH and POSTING QUANTITY. 

         

         

        All blue should be based on Criteria 2.

         

        Thank you once again.