Count words

[LESPINOLA]

Hi All, 

I need to count the number of SSD from the cell below:

FriendlyName : Msft Virtual Disk
SerialNumber :
MediaType : SSD
Size : 8589934592
FriendlyName : ST3320418AS
SerialNumber : 9VMY84KE
MediaType : HDD
Size : 320072933376
FriendlyName : KINGSTON SA400S37120G
SerialNumber : 50026B77760184FC
MediaType : SSD
Size : 120034123776

I created one new column with this line :

COUNT SSD = CALCULATE(
COUNTROWS(HD_Info);
SEARCH("SSD";HD_Info[MACHINE_CUSTOM_INVENTORY_0_19239];;0))

But it´s not working 100% correct , the result showed is 1 , not the correct "2" , do you know how can I fix this count to have the correct result ?

[camargos88]

Hi @LESPINOLA ,

Try this code for a measure:

Measure =

VAR _column = SELECTEDVALUE('Table'[Column1])

VAR _text = "SSD"

RETURN (LEN(_column) - LEN(SUBSTITUTE(_column; _text; ""))) / LEN(_text)

Your code didn't work because it's counting the number of rows containing SSD and not each ocurrence.

I've created this file using m code: Download PBIX 

I would use this second example (m code).

[LESPINOLA]

Hi Camargos88, 

I tried here this measure, but without sucess.

Let me show you one example that I need ! I need create two column , one to count the SDD and other to count HDD like the example below:

TEXT COLUMN	COUNT SSD	COUNT HDD
<br/>MediaType : SSD<br/><br/>MediaType : HDD<br/><br/>MediaType : HDD<br/>	1	2
<br/>MediaType : SSD<br/><br/>MediaType : SSD<br/><br/>MediaType : SSD<br/>	3	0
<br/>MediaType : HDD<br/><br/>MediaType : HDD<br/><br/>MediaType : HDD<br/>	0	3
<br/>MediaType : SSD<br/><br/>MediaType : SSD<br/><br/>MediaType : SSD<br/>	3	0
<br/>MediaType : HDD<br/><br/>MediaType : SSD<br/><br/>MediaType : SSD<br/>	2	1

Thanks

[camargos88 · Accepted solution]

@LESPINOLA ,

Dá uma olhada no arquivo:  Download PBIX  na aba 2.

Eu inclui outras linhas, com tamanhos diferentes pra testar.

[camargos88]

Hi @LESPINOLA ,

Check the uploaded file, I did some work on the column to transform it on columns, so you can easily filter it.

Also it`s important to know exactly if the column has break lines or another special characters, so we can transform it.

[LESPINOLA]

Hi @camargos88 , 

Unfortunately the lines have no defined patterns and sizes to be able to divide the columns.

I have to identify the SSD word inside the text and count how many times it appears.

Thanks

[camargos88]

@LESPINOLA ,

Vou dar uma olhada nas linhas que mandou. Se as linhas tem <br/><br/> já é um padrão para a quebra.