Count the value that does not starts with a certain number

[zahidah_mabd]

Hi, I want to count the number of data that does not begins with a certain value.

The valid position code begins with either "0" or "10000" so any code that does not begins with any of those two digits is considered invalid.

Example:
02789220 (valid)
10000568 (valid)
24876490 (invalid)
556 (invalid)

How do I create a measure that counts the number of invalid position code?

This is the measure that I created but it doesnt really gave the correct result. It ended up counting the total data which is wrong. 

Sum Invalid Position Code = 

CALCULATE(countrows(Filter('zhpla 2022', left('zhpla 2022'[Position Code],1) <> "0" || left('zhpla 2022'[Position Code],5) <> "10000" ))) + 0

Can anyone help me?

[v-yanjiang-msft · Accepted solution]

Hi @zahidah_mabd ,

According to your description, you should use && but not || in your code, modify it to:

Sum Invalid Position Code =
CALCULATE (
    COUNTROWS (
        FILTER (
            'zhpla 2022',
            LEFT ( 'zhpla 2022'[Position Code], 1 ) <> "0"
                && LEFT ( 'zhpla 2022'[Position Code], 5 ) <> "10000"
        )
    )
) + 0

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zahidah_mabd]

It worked! Thank you sooo much 🙂

[danextian]

Hi @zahidah_mabd ,

First, I woud create a calculated column that determines whether the start of a value is zero or not. That would be

Zero =
LEFT ( Table[Column], 1 ) = "0"
    || LEFT ( Table[Column], 5 ) = "10000"
//this assumes that column is a text type

Then a measure to count the number of rows

Row Count =
CALCULATE ( COUNTROWS ( Table ), FILTER ( Table, Table[Zero] = TRUE () ) )