Count rows only if each item doesn´t have certain value in a column

[Supercharlie50]

Let me explain. We have a column full of serial numbers which appear multiples times in this column; they are repetitive.

Each serial number goes through different states along time. It appears each time it has a status. This status is stored in a column called "Status".

For example, one serial number (1234567FX) went through status 2, 19 , 53 and finally 9. This means there are 4 rows with this serial number with each status (123456FX with status 2, 123456FX with status 19, 123456FX with status 53 ,123456FX with status 9).

Attached is an image of the columns´ structure

.

What i need is to do a distinct count of serial numbers which HAVEN´T gone through state 53. For example, if a serial number had status 2, 43 and finally 8, it would be counted (distinctively), but if it had status 2, 43, 53 and ended with status 8, it wouldn´t be counted, in any of the status it went though, it would be dismissed entirely.

Is there any way to do this in Power BI?

Thanks in advance.

[PaulDBrown · Accepted solution]

If you just need to see the serial numbers, create a table visual with the field, add the measure to the filter pane and set the value to 1.

If you need the distinctcount, include the measure in the table visual. (no need to use the filter pane).

You don't need the Status field

[Supercharlie50]

Thank you so much man, it worked! *Virtual hug* 🙂

[PaulDBrown]

Try:

Non 53 =
VAR _53 =
    CALCULATETABLE (
        VALUES ( Table[Serial Number] ),
        FILTER ( Table, Table[Status] = 53 )
    )
VAR _all =
    VALUES ( Table[Serial Number] )
RETURN
    COUNTROWS ( EXCEPT ( _all, _53 ) )

[Supercharlie50]

How could i display these serial numbers which meet these conditions, on a table? (just showing the columns serial number and status)