Forum Discussion

MPereira's avatar
MPereira
Frequent Visitor
7 years ago
Solved

How to search a value in the same column

Hi experts... I need your help.

 

I have a quality database of my products where one of my information is the LOTE.
In my quality process I can disapprove a complete LOTE (mother lote), or simply part of the LOTE (Children Lote).

 

                          


I identify a Mother Lote when the code does not have the hyphen (-) and the first 14 characters are the same as the first 14 characters of the child Lote.

 

In that case, when I reject the Mother Lote, I need to disregard the child lotes from my analysis. For example:

          

 

I tried several ways to mark these children lotes with some flag, but I could not find a correct way to do this programming.


Anyone have any idea how to do it?

Need Help
  • AlB's avatar
    AlB
    7 years ago

    MPereira

     

    Try this for your calculated column. Bear in mind that I am assuming here that IDs for Mother Lotes always have 14 characters and IDs for Children Lotes always 17 as you show.

     

    Table1[Delete] = 
VAR IsChildrenLote = (LEN(Table1[Lote])=17)
RETURN
IF(IsChildrenLote;
     IF(CONTAINS(Table1;Table1[Lote];MID(Table1[Lote];1;14));"X")
    )

     

13 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi MPereira

    Where in your table(s) can you see whether a mother lote is rejected?  

    • MPereira's avatar
      MPereira
      Frequent Visitor
      7 years ago

      Hi AIB.

       

      The mother lote is never rejected.
      Whenever a mother lote appears in my data, I should automatically delete the children lotes.

      Ok?

       

      tks,

      Marcelo.

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Hi MPereira

         

        If v-danhe-msft's solution works perfectly with the sample data you posted but not with the other data you are trying I guess you should either provide a sample of the new data that is representative of the differences with the one posted (best option) or explain what the differences are. Otherwise we cannot help much.

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi MPereira,

    Based on my test, you could refer to below steps:

    Sample data:

    Create below calculated columns:

    a = IF(LEN('Table1'[Lote])=14,'Table1'[Lote],BLANK())
    b = IF(LEFT('Table1'[Lote],14)=MAX('Table1'[a])&&'Table1'[a]=BLANK(),"X",BLANK())

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

    • MPereira's avatar
      MPereira
      Frequent Visitor
      7 years ago

      Hi Daniel,
      Thanks for your reply!
      For example data I used your solution works perfectly, however were just examples.
      When I use a larger data mass, this solution does not work correctly.
      Any new suggestions?
      thank you,

      Marcelo.