topic Re: How to count the number of words in a table in DAX Commands and Tips

How to count the number of words in a table

UNOFOR — Tue, 09 Jul 2019 11:16:01 GMT

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

Re: How to count the number of words in a table

Anonymous — Tue, 09 Jul 2019 11:36:50 GMT

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

Re: How to count the number of words in a table

UNOFOR — Tue, 09 Jul 2019 11:38:04 GMT

Ok, thanks anyway

Re: How to count the number of words in a table

Anonymous — Tue, 09 Jul 2019 12:01:40 GMT

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

Re: How to count the number of words in a table

UNOFOR — Tue, 09 Jul 2019 12:14:08 GMT

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

Re: How to count the number of words in a table

Anonymous — Tue, 09 Jul 2019 12:20:32 GMT

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

Re: How to count the number of words in a table

Anonymous — Tue, 09 Jul 2019 12:23:40 GMT

This might help you:

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

Best

Darek

Re: How to count the number of words in a table

UNOFOR — Tue, 09 Jul 2019 12:32:48 GMT

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

Thanks a lot

Re: How to count the number of words in a table

Anonymous — Tue, 09 Jul 2019 12:41:47 GMT

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

Best

Darek

Re: How to count the number of words in a table

Anonymous — Tue, 09 Jul 2019 13:08:41 GMT

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

Re: How to count the number of words in a table

UNOFOR — Tue, 09 Jul 2019 13:24:13 GMT

Aaah, there it is!!

Thank you very much!

Re: How to count the number of words in a table

Anonymous — Tue, 09 Jul 2019 17:57:27 GMT

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

Thanks.

Best

Darek

Re: How to count the number of words in a table

UNOFOR — Tue, 09 Jul 2019 20:49:02 GMT

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