topic Count Keywords in Hashtags in DAX Commands and Tips

Count Keywords in Hashtags

Anonymous — Thu, 24 Jun 2021 09:58:13 GMT

Hello folks,

I have a dataset like the below mentioned and I need to calculate occurances of Hashtags, lets say a single hashtag has occured how many times, in a row and across the column. I need to write a DAX which could help me solve this issue.

ID	Strengths
1
2	#expectations-fulfilled ,#open-communication,#teamwork,#inclusivity ,#my-extended-team,#project-collaboration,#design-efficiency,#quality-achieved
3	#open-communication,#teamwork,#positive-attitude,#technically-proficient,#quality-achieved,#GO-iCRC,#expectations-fulfilled ,#proactive-team,#process-compliance
4	#timeliness-at-par,#positive-attitude,#expectations-fulfilled
5	#positive-attitude,#inclusivity ,#teamwork,#my-extended-team,#open-communication,#thumbs up-project
6	#project-collaboration,#quality-achieved,#proactive-team
7	#project-collaboration,#quality-achieved,#proactive-team
8	#thumbs up-project,#teamwork,#positive-attitude
9	#thumbs up-project,#teamwork,#technically-proficient
10

Re: Count Keywords in Hashtags

Tanushree_Kapse — Thu, 24 Jun 2021 12:28:09 GMT

Hi Anonymous ,

You could create a column to know thw count of # across each row:
ROWcount= LEN(Table[Strengths])-LEN(SUBSTITUTE(Table[Strengths],"#",""))

After this:
You can create a measure to count #s in a column:
Columncount= Sum(ROWcount)

I guess this is what you want.

Re: Count Keywords in Hashtags

Anonymous — Thu, 24 Jun 2021 12:30:42 GMT

It's not a job for DAX, but Power Query. First, you have to put each tag into its own row (keeping the link to ID, of course). Once you have a column where each row contains only 1 hashtag, you can start thinking about calculations. By the way, if a hashtag can appear multiple times in one row in the above table, you'll also need a column in the table I suggest you build that will store the number of occurences. So 3 fields: ID, Hashtag, Count.

Re: Count Keywords in Hashtags

Anonymous — Fri, 25 Jun 2021 03:24:20 GMT

Hello Anonymous ,

Thanks for the quick reply, I had this thing in mind to break each hashtags into rows but I guess that would go against the Start Schema thing, which we generally try to achieve and moreover goes against the concept of normalisation. But still i agree to your point, it is so smooth and easy with Python but within power bi, it does requires lots of critical thinking and effort.

Re: Count Keywords in Hashtags

Anonymous — Fri, 25 Jun 2021 08:52:31 GMT

On the contrary, what I described is the right thing to do and it's in the spirit of dimensional modeling, including the star schema. Normalization is a concept from the OLTP world. PBI lives in the OLAP world where denormalization is what you want to achieve. But here we have a problem that has nothing to do with these concepts. Here we have a problem of atomicity of data. And columns should always store atoms, not compounds, be in in OLTP or OLAP.

Incidentally, to get the data into the right shape in PQ only takes several clicks of the mouse.