Forum Discussion

TaufikMaggangka's avatar
TaufikMaggangka
Icon for Helper II rankHelper II
5 years ago
Solved

Count specific text in one column with direct query mode

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 Fever2
Loss of smell1
Diarrhea fever2
Fever1
Headache Fever Diarrhea3

 

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

Thank you

  • Samarth_18's avatar
    Samarth_18
    5 years ago

    You can create a measure with below code

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

     

2 Replies

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    5 years ago

    You can create a measure with below code

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