Count rows with additional values in the string

[Lobo1908]

Hi, 

I'm looking for a measure that would allow me to count rows with some values in the string. 

I've got a table with a column serial numbers and a column with codes assigned to those serial numbers. 

The measure should count only the rows if in that row there is a code different to "Q0001".

Now if a row has a code for e.g. "Q0002" and code "Q0001" this should also be taken into the count.

The additional, issue I have with my data is that the values in the string are not clean.

What I mean by that is some of them are like " Q0001" or " Q0001       "

Additionally, there are some values in the string like "Q 188" which must be ignored. 

Only values in the format QNNNN are acceptable.

Please find below an example of my data.

The result for this data should be 10.

I've also attached a simplyfied report in the link :

https://drive.google.com/file/d/114Jn58-yxf0ZpwiMlLqEQx9tAp1tWF68/view?usp=share_link

Serial	Q Codes
123	Q0001
124	Q0001
125	Q0003
126	Q0004
127	Q0005
9	Q0003 Q0001
10	Q0003 Q0002 Q0001
11	Q0003 Q0001 Q0002
12	Q0003Q0001 Q0002
13	Q0001
14	Q0001
15	Q0001
16	Q0001 Q 222
17	Q 188 Q0001
444	Q0001 Q0001
443	Q0023 Q0042
442	Q0033 Q0102
441	Q0001
440	Q0999 Q0002

[Mikelytics · Accepted solution]

Hi @Lobo1908 

Please try the following

CountWIhtouQ0001 = 
SUMX(
    VALUES('Table'[Serial]),
    IF(
        CONCATENATEX(
            'Table',
            TRIM(SUBSTITUTE([Q Codes],"Q0001",""))
        )
        <>"",1,0
    )
)
    

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.