The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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?
Solved! Go to Solution.
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")
)
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
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.
Hi @MPereira
Where in your table(s) can you see whether a mother lote is rejected?
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.
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.
Hi @AlB!
f course yes!
Following is a file with a larger mass of data than the initial example.
Thank you for your help.
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
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")
)
Here is a file with some dummy data based on what @v-danhe-msft had and with the new column.
Thank you so much!!!
My pleasure. glad it helped. It's always nice to come across someone so grateful
User | Count |
---|---|
158 | |
109 | |
96 | |
84 | |
75 |
User | Count |
---|---|
157 | |
137 | |
131 | |
81 | |
61 |