Unstructured Data repetitive Word Count

[tejapowerbi123]

Hello Community,

Please review below and provide your guidance.

My data table has unstructured  data and also Look up table.

Requirements:If lookup table words matches with Data Table then if should count words in a new table or in Data Table.

I am not able to figure it out how to procedd?

Once i have word count it is going to use on bar chart (Example:Bar for Financail Analysis=3,Food Analysis=2)

I have posted the question here and I got the answer but when same words repeat it will not count correctly.

https://community.powerbi.com/t5/Desktop/Unstructured-Data-Word-Count/m-p/1202169#M537384

Data Table

Comments

Financial Analysis,Financial Analysis & Health Analysis is very Important.

Food Analysis & Financial Analysis is very Important & Food Analysis is also important

Lookup Table

Financial Analysis

Health Analysis

Food Analysis

Result Table
Column_1	Word_Count
Financail Analysis	3
Health Analysis	1
Food Analysis	2

[az38]

Hi @tejapowerbi123 

try

Measure = 
var _lookupValue = MAX('Lookup Table'[Lookup Column])
RETURN
CALCULATE(SUMX('Data Table', (LEN('Data Table'[Comments]) - LEN(SUBSTITUTE('Data Table'[Comments], _lookupValue, "")))/LEN(_lookupValue)))

[tejapowerbi123]

Hi,

I have used below Measures and not able to get expected result.

Measure =

var _lookupValue = MAX('Lookup Table'[Text Search])

RETURN

CALCULATE(SUMX('Data Table', (LEN('Data Table'[Comments]) - LEN(SUBSTITUTE('Data Table'[Comments], _lookupValue, "")))/LEN(_lookupValue))

Comments

Financial Analysis & Health Analysis is very Important or financial analysis & Financial Analysis & Food Analysis

Food Analysis & Financial Analysis is very Important

[az38 · Accepted solution]

@tejapowerbi123 

the output exactly the same as you mentioned in the first post. if you need case insensitive statement use

Measure = 

var _lookupValue  = LOWER(MAX('Lookup Table'[Text Search]))

RETURN
CALCULATE(SUMX('Data Table', (LEN('Data Table'[Comments]) - LEN(SUBSTITUTE(LOWER('Data Table'[Comments]), _lookupValue, "")))/LEN(_lookupValue)))

[Ashish_Mathur]

Hi,

If data is unstructured, then is it fair to assume that there would be spelling mistakes as well or extra spaces/fewer spaces?  An example being Financial Analysis and FinancialAnalysis

[amitchandak]

@tejapowerbi123 , refer this can help

https://radacad.com/find-a-text-term-in-a-field-in-power-bi-using-dax-functions

[AllisonKennedy]

You could try using a technique similar to this one to repeat the SEARCH() function until the result is Blank, then grab the number _n and add them all up.
https://community.powerbi.com/t5/Community-Blog/For-and-While-Loops-in-DAX/ba-p/636314