How do I perform a distinct count of text within a column?

[gusdahu]

Power BI Community,

My goal is identify a DAX that will perform a distinct count of each word below. For example, the results for the word "Gloves" should be two. For "Scaffolding" the results should be three and so on. 

Vendor ID	Vendor Name	PO Number	Description
0000001234	Dummy Supplies	2000000001	Service Scaffolding
0000001234	Dummy Supplies	2000000002	Service Sand Blasting
0000001234	Dummy Supplies	2000000003	Painting
0000001234	Dummy Supplies	2000000004	Service Gloves
0000001234	Dummy Supplies	2000000005	Miscellaneous Supplies
0000001234	Dummy Supplies	2000000006	Miscellaneous Supplies
0000001234	Dummy Supplies	2000000007	Gloves
0000001234	Dummy Supplies	2000000008	Service Scaffolding
0000001234	Dummy Supplies	2000000009	Mask
0000001234	Dummy Supplies	2000000010	Service Scaffolding

I used the following DAX, but did not get the response I wanted:

                Count Words = CALCULATE(DISTINCTCOUNT(Sheet1[Description]),Sheet1[Description]="Gloves")

The results "Gloves" came back as one instead of two. Any assistance will be appreciated.

Regards,

Gus Dahu

[Ashish_Mathur]

Hi,

Try the Word Cloud visual from the Custom Visuals store.

[bwarner]

Distinct will always give you one, no more and you need a CONTAIN.

Try this instead:

Count Words = CALCULATE(COUNT(Sheet1[Description],CONTAINS(Sheet1[Description],Sheet1[Description]="Gloves"))

[gusdahu]

Hi @bwarner ,

I tried the DAX you provided below with no Luck. I get an error "Too few arguments were passed to the CONTAINS function. The minimum argument count for the function is 3." I tried to correct the erro, but was unsuccessful in doing so. Do you suggest another DAX I could use?

Regards,

Gus Dahu

[parry2k]

@gusdahu Your question is not clear, distinct will surely count it once, are you looking how any time gloves word shows up in description?

How you are planning to visualize the data?

[gusdahu]

Hi @parry2k ,

Correct. I would like to see how many times a particular word, in this instance "Gloves", comes up in my population.

To answer your second question, ideally I will have a large population with multiple vendors and Purchase Order Description. I would then would like to determine which words are most closely used or associated with each vendor. This will give me an idea of what we are buying most from each vendor.

Regards,

Gus Dahu

[parry2k]

@gusdahu the following measure will give you the count of gloves and you can tweak it but it is fixed value "Gloves" which I think you don't want

Word Count = CALCULATE( COUNTROWS( Table6 ), CONTAINSSTRING( Table6[Description], "Gloves"  ) ) 

So best way to overcome that is to split the description column in rows in power query and then all this is super easy.

[gusdahu]

Hi @parry2k ,

Thank you for your response. The challenge I have with the DAX you provided is that it only captures one word "Gloves". Since I will be counting words for an entire population, unfortunately this DAX will not help me. 

The word cloud visual in Power BI's market place basically provides me with what I am looking for. However, I would like my results to be in a matrix/tabular format where I can see the actual count of times a particular word has been associated with a vendor. If you know of a way to get to this result, then that would be great.

Results,

Gus Dahu 

[gusdahu]

Hi @parry2k ,

Correction to the previous post, the word cloud does not offer me exactly what I need as it does not break up descriptions into individual words. I need to figure out how to determine a sentence into individual words and then perform a count of those words.

Regards,

Gus Dahu

[parry2k]

@gusdahu Yes, as I suggest, we will do the break up (split) the description in words in power query and then it is a breeze. You really need to be very specific:

- do you have list of words which care about, if yes then the solution posted by @Anonymous will work the best.

- if you don't have the list, you want it be dynamic and count on any word then we need to split  the description in word in power query (which is super simple) and everything will work from there.

[Anonymous]

@gusdahu 

Here's my take on this. I took your desciption column and created a new dimension column based on each word

Using those on rows, you can use the following measure:

Measure 2 = 
CALCULATE( 
    COUNTROWS(Table1),
        FILTER(
            Table1,
            CONTAINSSTRING(Table1[Description],SELECTEDVALUE( DimKeyWords[Keyword])
        )
)
)

which gives this table

[gusdahu]

Hi @Anonymous ,

Thank you for your response!

I belive this is what I need. How did you create the new dimension column based on each word?

Regards,

Gus Dahu

[parry2k · Accepted solution]

@gusdahu you can create a new data table and manually enter the keywords. Again if you have fix list then this solution is fine but if you want it to be more dynamic, you can create keyword by splitting  description field by space in Power Query and it will generate a keyword list for you.

You have to be specific what you are looking for. Anyhow you have solution available, it is upto you to choose what meets your requirement