Measure to calculate a number of unique words in a string

[my_username]

How can I create a measure which, given a string, would calculate the number of unique words in the string.

All the words are separated by a delimiter "|" (or any other delimiter of choice).  The words consist of letters and numbers, and can be different lengths.

For example, in a string "a|b|a|c|d" there are 4 unique words: a,b,c,d.  

Another example: "m5|n3|m5|m7" there are 3 unique words: m5, n3, m7. 

I was trying to implement a solution which would split a text into column with each row being an individual word, and then I would just calculate a distict number of rows in the column, but I do not know how to implement it.

Thanks.

[Ashish_Mathur]

Hi,

You should ideally be solving this via the Query Editor (rather than via a measure).  Would you be OK with a Query Editor solution?  

[my_username]

No, because I need this value as a measure, for visualisation. This question is related to another question I asked, but the solution I was offered was not suitable for my case, as I have a big dataset. Link to another question: https://community.powerbi.com/t5/Desktop/Calculate-a-number-of-occurrences-of-unique-items-within-co...

[amitchandak]

@my_username , I have done a similar power query solution here.

Created distinct sorted comma-separated text from unsorted text: https://youtu.be/4bV30FEFjEM

You do not have sort. do  just split, distinct and combine

In case you need sort, not need to transform to number.

[my_username]

Do you have a similar solution in DAX?

[AlexisOlson · Accepted solution]

This is possible in DAX using PATH functions.

Unique Words = 
VAR _Str = CONCATENATEX ( DISTINCT ( Table1[String] ), Table1[String], "|" )
VAR _List_ = GENERATESERIES ( 1, PATHLENGTH ( _Str ) )
VAR _Words_ = ADDCOLUMNS ( _List_, "Word", PATHITEM ( _Str, [Value] ) )
RETURN
    IF ( _Str = "", 0, COUNTROWS ( SUMMARIZE ( _Words_, [Word] ) ) )