How to count the number of words in a table

[UNOFOR]

Hi

How do I count the words (not rows) in a specific table or column if there is more than one word in each cell.

Best

/Uno

[Anonymous · Accepted solution]

It would be good if you could mark the post as the solution if it has solved your problem. 

Thanks.

Best

Darek

[Anonymous]

DAX is not the right tool to deal with this. Use Power Query. I'm not even sure it's possible. If it is, it'll be extremely hard.

Best

Darek

[UNOFOR]

Ok, thanks anyway

[Anonymous]

If you really need it in DAX, then here it is:

[Word Count] =
var __selectedWord = SELECTEDVALUE( TableWithIndividualWords[Name] )
var __selectedWordLength = LEN( __selectedWord )
var __wordCount =
	SUMX(
		YourTable, -- this table will honor any filters you put on it 
		var __name = YourTable[Name]
		var __nameLength = LEN( __name ) 
		var __wordCountInRow =
			( __nameLenth - LEN(SUBSTITUTE( __name, __selectedWord, "")))
				/ __selectedWordLength
		return
			__wordCountInRow
	)
return
	__wordCount

But to get this working you have to have a table that stores all the possible words you could encounter in advance. And the table should stand on its own, without any relationships to any other table. I named the table TableWithIndividualWords and it has only one column [Name] where you store all the possible words. To make this into a visual table you just drag the column from the table so that individual words are visible and drop the measure. It'll calculate the number of times the word appears anywhere in the first table (YourTable).

Best

Darek

[UNOFOR]

Thank you vary much. 

---

@Anonymous wrote:

If you really need it in DAX, then here it is:

[Word Count] =
var __selectedWord = SELECTEDVALUE( TableWithIndividualWords[Name] )
var __selectedWordLength = LEN( __selectedWord )
var __wordCount =
	SUMX(
		YourTable, -- this table will honor any filters you put on it 
		var __name = YourTable[Name]
		var __nameLength = LEN( __name ) 
		var __wordCountInRow =
			( __nameLenth - LEN(SUBSTITUTE( __name, __selectedWord, "")))
				/ __selectedWordLength
		return
			__wordCountInRow
	)
return
	__wordCount

But to get this working you have to have a table that stores all the possible words you could encounter in advance. And the table should stand on its own, without any relationships to any other table. I named the table TableWithIndividualWords and it has only one column [Name] where you store all the possible words. To make this into a visual table you just drag the column from the table so that individual words are visible and drop the measure. It'll calculate the number of times the word appears anywhere in the first table (YourTable).

Best

Darek

Wow, thanks. I guess the sentence "stores all the possible words you could encounter in advance" will be the thing that makes it hard to use. Seems quite easy but appearently it is not. The problem occured when hade created a word map and wanted to creat a slicer from the words accouring in it.

So you advice would be to instead create a separate table "by hand" using excel?

/U

---

[Anonymous]

This might be better: https://www.youtube.com/watch?v=YOmL57g3RMg

Best

Darek

[UNOFOR]

Aaah, there it is!!

Thank you very much!

/U

[Anonymous · Accepted solution]

It would be good if you could mark the post as the solution if it has solved your problem. 

Thanks.

Best

Darek

[UNOFOR · Accepted solution]

Hi. Of course I will. (as soon as I figured out how)

[Anonymous]

This might help you:

https://www.youtube.com/watch?v=HPDG_KXiLBo

Best

Darek

[UNOFOR]

Ok, I will give it a try even if it´s a bit above my level.  Your help is much appreciated.

Thanks a lot

/U

[Anonymous]

Hi there.

Mate, the last link I sent you shows you exactly how to obtain a table with all the distinct words that you can find in your table. Please follow it and you'll have the table I was talking about. Then everything is very simple.

Best

Darek

[Anonymous]

No. My advice is to extract all individual words from the table using Power Query. This is the right tool to do it. Not Excel.

Best

Darek