How to count all the non duplicated values in a table and filter by another value

[Jstitch]

So i have this table and i have a running list of all the fluid ends as they come in.  When we recieve them we set them to on the floor.  When they are installed we add another row in order not to run through the data to find the previous row with the serial number and update the status.  What we have here is the serial numbers and status running on and on.  What i want is to get the count of fluid ends (pretty much off of the serial numbers) that are on the floor.  Which if they serial numbers have not been entered at a later time in the table they should still be on the floor which means the serial number value has not been duplicated.

F.E. S/N               Status

1111                   On the Floor

2222                   On the Floor

3333                   On the Floor

1111                   Installed on Pump

2222                   Sent to refurbish

4444                   On the floor

5555                   On the floor

3333                   Installed on pump

so from this table i should have only 2 units on the floor since 1111,2222,3333 have been installed on pumps or refurbished.

Hope this makes sense

thank you for the help

[Zubair_Muhammad]

Hi @Jstitch

Try this MEASURE

Measure =
COUNTROWS (
    FILTER (
        VALUES ( Table1[F.E. S/N] ),
        CALCULATE ( COUNT ( Table1[Status] ) ) = 1
    )
)

[Jstitch]

i inputed this and it is still showing me the count of all on the floor values basically showing 5 instead of 2

[Zubair_Muhammad · Accepted solution]

Hi @Jstitch

I get count 2 with your sample data. Could you share your screen shot or file?

See the pic below

[Zubair_Muhammad]

@Jstitch

Also you can use this MEASURE to get the Names of those fluid ends

Measure 2 =
CONCATENATEX (
    FILTER (
        VALUES ( Table1[F.E. S/N] ),
        CALCULATE ( COUNT ( Table1[Status] ) ) = 1
    ),
    Table1[F.E. S/N],
    ", "
)