MaxRow wirh multiple condition

[desmond_lee]

Hi Guys

I try to pack MaxRow timestamp by Standard Tool ID with a condition COl M = "PASS".  I write this expression 

[_THISROW]=MAXROW("Calibration Database","TIMESTAMP",
AND(
[_THISROW].[STANDARD TOOL ID]=[STANDARD TOOL ID],
[CALIBRATION CONCLUSION]="PASS")
)

But it does not work.  This tool MT1509-27 RULER ST suppose to be filtered because Col M="FAIL". Where is the mistake ?

[AleksiAlkio]

Are you sure it's the correct row? The row number on your spreadsheet shows 197, but on the printscreen, it shows just 21.

[desmond_lee]

Yes that correct on Row 21 being filter as the latest timestamp with the condition Col M = "PASS".  This is not what I want to achieved. I want to achieve The latest date with the condition Col M = "PASS" group by Standard Tool ID. In this example above 197 row is the latest date in the row group by MT1509-27-RULER-ST but Col M = FAIL". And the expression I write choose row 21 tht fullfilled both condition but that is not the latest timestamp

[AleksiAlkio]

Not sure what you mean. You would like to filter with the "Pass" but you would like to read the max timestamp from it. So.. why don't you remove that "Pass" filter away from your formula?

[desmond_lee]

Sorry to make you so confuse. What I mean is I would like to Maxrow timestamp with a column=PASS. 

[AleksiAlkio]

With the Standard Tool ID or not?

[desmond_lee]

Yes. Mean group by Standard Tool ID.

[AleksiAlkio]

As per your printscreens, for me it sounds like your formula is working just fine. It shows the row 21 because that contains the latest timestamp for that Tool ID which has the "Pass".

[desmond_lee]

Yes its true but that is not my intention. Row 21 fullfiled Col M="PASS"  but row 21 is not the maxrow timestamp base on the Standard Tool ID grouping. Row 197 is the latest but Col M="FAIL". Thus this should be completely filtered off. This is my intention.

In laymen By Standard Tool ID group Latest Timestamp and Col M = "PASS" 

[AleksiAlkio]

So.. you would like to filter the row 21 away as well because it's not the latest timestamp in that Tool ID group? Then you won't have any rows from that Tool ID group. Is that your goal?

[desmond_lee]

Yest that is what I want. I have 3 thousands tool and some tool like row 21 is meant to be remove from the list and left other good tool. That is the process. In fact tool on row 21 is to be scraped.

[AleksiAlkio]

Finally I was able to understand your goal 😄

[desmond_lee]

Thank you and I don't know how to create that expression

[AleksiAlkio · Accepted solution]

How about.. 

AND([TIMESTAMP]=MAX(SELECT(Calibration Database[TIMESTAMP],
[_THISROW].[STANDARD TOOL ID]=[STANDARD TOOL ID])),
[CALIBRATION CONCLUSION]="PASS"
)

[desmond_lee]

Wow ! Thank you sooooooo much. Row 21 is removed. I sample Row 42 tool ID  MT1496-42-RULER-ST the same situation as Row 21 its also remove.

[AleksiAlkio]

You're welcome!