Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
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)))
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)))
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 |
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)))
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
@tejapowerbi123 , refer this can help
https://radacad.com/find-a-text-term-in-a-field-in-power-bi-using-dax-functions
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |