How to search a value in the same column

[MPereira]

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?

[v-danhe-msft]

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]

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.

[AlB]

Hi @MPereira

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

[MPereira]

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]

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.

[MPereira]

Hi @AlB!

f course yes!
Following is a file with a larger mass of data than the initial example.

Thank you for your help.

[AlB]

Ok @MPereira

But could you share, rather than a screen capture, either the file itself (preferably) or otherwise paste the data here in text format, so that it can be easily copied? You can copy a table (or fragment thereof) and paste it in the editor where you write your message

Thanks   

[MPereira]

Excuse me @AlB!

I was at the airport and I could not respond in time!

[AlB · Accepted solution]

@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")
)

[MPereira]

GREAT @AlB!!!

That worked!!!

Thanks for your help!

best regards.

[AlB]

@MPereira

Here is a file with some dummy data based on what @v-danhe-msft had and with the new column. 

[MPereira]

Thank you so much!!!

[AlB]

@MPereira

My pleasure. glad it helped. It's always nice to come across someone so grateful