topic Re: Count specific text in one column with direct query mode in DAX Commands and Tips

Count specific text in one column with direct query mode

TaufikMaggangka — Wed, 14 Jul 2021 05:56:45 GMT

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

Re: Count specific text in one column with direct query mode

Samarth_18 — Wed, 14 Jul 2021 08:08:26 GMT

You can create a measure with below code

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

Re: Count specific text in one column with direct query mode

TaufikMaggangka — Wed, 14 Jul 2021 08:24:11 GMT

Basically it count the space but it works.

Thank you Samarth_18