Count specific text in one column with direct query mode

[TaufikMaggangka]

Hi all,

I need help to create measure to count number of specific text that contains in one column, please note that I using direct query mode on connecting to database.

Here my illustration, I named it my table with "CovidSymptomsScreen"

Cough Fever

Loss of smell

Diarrhea fever

Fever

Headache Fever Diarrhea

I hope the measure result will count the specific text that contains in a cell, here the illustration result if I create a table vizualisation :

Cough Fever	2
Loss of smell	1
Diarrhea fever	2
Fever	1
Headache Fever Diarrhea	3

I am quite new with power bi, hopefully tutorial dax will shown from expert answer.

Thank you

[Samarth_18 · Accepted solution]

You can create a measure with below code

word_count = 
LEN ( MAX ( 'Table (5)'[Cough Fever] ) )
    - LEN ( SUBSTITUTE ( MAX ( 'Table (5)'[Cough Fever] ), " ", "" ) ) + 1

[TaufikMaggangka]

Basically it count the space but it works.

Thank you @Samarth_18