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.
Hi Experts
Hi, I have a column of text separated by a semi colon, e.g.
Col1
word1;word2
word1;word3
word3;
word4
word1
etc etc
I would like to count how many instances of each word in each row and have a total but comparing it against a table of possible words, result would be:
word 1 = 3
word 2 = 1
word 3 = 2
etc
the following measure does not work when you have word1;word2
Count = SUMX(SearchWords,IF(LEN(SUBSTITUTE(SearchWords[SearchWords],[Words],""))<LEN(SearchWords[SearchWords]),1,0))
HELP
Solved! Go to Solution.
Hey @Anonymous
You can use the CONTAINSSTRING function. I have added a new table with all unique words, which I reference in the DAX measure.
Measure =
VAR _search_word =
SELECTEDVALUE ( search_words[search_word] )
VAR _result =
CALCULATE (
COUNTROWS ( 'Table' ),
CONTAINSSTRING ( 'Table'[Col1], _search_word )
)
RETURN
_result
Result:
Hey @Anonymous
You can use the CONTAINSSTRING function. I have added a new table with all unique words, which I reference in the DAX measure.
Measure =
VAR _search_word =
SELECTEDVALUE ( search_words[search_word] )
VAR _result =
CALCULATE (
COUNTROWS ( 'Table' ),
CONTAINSSTRING ( 'Table'[Col1], _search_word )
)
RETURN
_result
Result:
Hey @Barthel
Thank you for your resposne.
Just a query 🙂
I guess is the above logic wont work if the same name comes in the row more than once correct?
meaning word1;word2;word1. How should we handle this with above logic?
I think this is very difficult to realize in DAX, and I honestly don't have a good answer for that right now. On the other hand, I think the easiest way is to edit the table in Power Query, splitting each cell into new rows using the separator. Each word than equals one row/column. This way you can still use the above formula. In fact, you don't have to use CONTAINSSTRING anymore and you can simply use an equals statement.
Thank you sir perfect
Hey, You can probably try the below approach :
1) split the column based on semi colon
2) Then transpose so that each row is equivalent to a word
3) Then create a measure by Summarize
something like:
SUMMARIZE(Table, Table[Column], "WordCount", COUNT(Table[Column]))
HI just tried this also not working too
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 |