An expression like the following will work:
=Count(Aggr(Only(If(Aggr(FirstSortedValue(status, -[product version id]), [product id]) <> 'deleted', [product id])), [product id]))
However, it's quite tricky and depending on the number of rows your application is handling, it may take a long time to calculate. So, you rather get the value in the script, using Peek() and Previous() functions:
Original: LOAD [product id], [product version id], status FROM Distinct.xls (biff, embedded labels, table is Sheet1$); Final: LOAD *, If([product id] <> Previous([product id]), If(status = 'deleted', 0, 1)) AS ProductCheck RESIDENT Original // do not use resident loads with large tables ORDER BY [product id], [product version id] DESC; DROP TABLE Original;
The expression now is as simple as
Hope that helps.