Forum Discussion

Lobo1908's avatar
Lobo1908
Helper I
3 years ago
Solved

Count rows with additional values in the string

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

 

SerialQ Codes
123Q0001
124 Q0001
125Q0003
126Q0004
127Q0005
9Q0003 Q0001
10Q0003 Q0002 Q0001
11Q0003 Q0001 Q0002
12Q0003Q0001 Q0002
13Q0001
14Q0001    
15Q0001
16Q0001 Q 222
17Q 188 Q0001
444Q0001 Q0001
443Q0023 Q0042
442Q0033 Q0102
441Q0001
440Q0999 Q0002
Need Help
  • Mikelytics's avatar
    Mikelytics
    3 years ago

    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.

     

1 Reply

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    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.