Count if Condition is Met

Get Help
1

Hello! I'm newish to Metabase and cannot figure out how to solve this problem!

I'm pulling records from two joined tables: the first has the current status of the item, and the joined one has all the history statuses for the item. So every time the item gets a different status, it logs in the history table and updates the current status in the first table. Here's a sample of how 3 items would have different statuses upon moving through the system.
Image 6-18-23 at 12.00 PM

Here's the problem: I need to count only items that have never had a fail status applied. Everything I've tried doesn't work because it just pulls one of the statuses in the order that doesn't count as a fail, and still shows me all 3 items, when in reality only item # 2 should show.

Thanks for the help! And I'm trying my best to only use the question builder for this and not a native query unless absolutely necessary. I write SQL just fine, but it seems Metabase has more functionality when using the builder.

2

Have you seen custom columns and countif expression?

3

Yes the problem is I can't find any expression or function to do what I need, which is to take into account if the product ID EVER had a fail applied, not to count the other non-fail statuses as the item not failing. Even though there's multiple records for one ID I need it to understand the condition...