Count how many times a word is in a Column

[Anonymous]

Hi Experts

Hi, I have a column of text separated by a semi colon, e.g.

Col1

word1;word2

word1;word3

word3;

word4

word1

etc etc

I would like to count how many instances of each word in each row and have a total but comparing it against a table of possible words, result would be:

word 1 = 3

word 2 = 1

word 3 = 2

etc

the following measure does not work when you have word1;word2

Count = SUMX(SearchWords,IF(LEN(SUBSTITUTE(SearchWords[SearchWords],[Words],""))<LEN(SearchWords[SearchWords]),1,0))

HELP

[Barthel · Accepted solution]

Hey @Anonymous 
You can use the CONTAINSSTRING function. I have added a new table with all unique words, which I reference in the DAX measure.

Measure = 
VAR _search_word = 
    SELECTEDVALUE ( search_words[search_word] )
VAR _result =
    CALCULATE ( 
        COUNTROWS ( 'Table' ),
        CONTAINSSTRING ( 'Table'[Col1], _search_word )
    )
RETURN
    _result

Result:

[NandanHegde]

Hey @Barthel 
Thank you for your resposne.
Just a query 🙂

I guess is the above logic wont work if the same name comes in the row more than once correct?

meaning  word1;word2;word1. How should we handle this with above logic?

[Barthel]

@NandanHegde,

I think this is very difficult to realize in DAX, and I honestly don't have a good answer for that right now. On the other hand, I think the easiest way is to edit the table in Power Query, splitting each cell into new rows using the separator. Each word than equals one row/column. This way you can still use the above formula. In fact, you don't have to use CONTAINSSTRING anymore and you can simply use an equals statement.

[Anonymous]

Thank you sir perfect

[NandanHegde]

Hey, You can probably try the below approach :

1) split the column based on semi colon

2) Then transpose so that each row is equivalent to a word

3) Then create a measure by Summarize

something like:

SUMMARIZE(Table, Table[Column], "WordCount", COUNT(Table[Column]))

[Anonymous]

HI just tried this also not working too

New Count =

var _a = SELECTEDVALUE('DimExoskeleton'[Process])

var _b = HASONEVALUE('DimExoskeleton'[Process])

var _c = countrows( FILTER('Daily Survey', 'Daily Survey'[InwhatprocessdidyouusetheExoskel (new)] = _a ))

Return

if (_b, if (ISBLANK(_c), 0, _c), BLANK() )