Calculating number of times a word appears in multiple columns.

[eric142]

Hi,

I have 10 different columns in my table that will have 8 differnt posible words. I want the total count of how many times each word appears in each column. I am currenlty doing this with 10 mesures then adding them all together.  Then rebeat for all words and all calumns that will be 90 measures. Is there a way to do it in one messure for each word?

Trouble1 Picker = CALCULATE(COUNTAX('Calibration_Issues','Calibration_Issues'[1 Trouble]),'Calibration_Issues'[1 Trouble]= "Picker")

Thanks for any advice

Eric

[eric142]

Thank you both for your replies, Ashish solution worked.  I will also record your solution MFelix as I see it as being usefull at a later time.

[Ashish_Mathur · Accepted solution]

Hi,

In the Query Editor, you should select all columns other than those which have the 10 text entries, right click and select "Unpivot Other Columns".  To your visual, drag the Attribute field and write this measure

=countrows('Calibration_Issues')

Hope this helps.

[MFelix]

Hi @eric142 ,

Have you tried doing a disconnected table with the 10 words you need and then making a change to your formula to be similar to this one:

Trouble1 Picker = CALCULATE(COUNTAX('Calibration_Issues','Calibration_Issues'[1 Trouble]),'Calibration_Issues'[1 Trouble] in values (Table[Words]))

Then you would only have 10 measures and with a slicer or similar you could filter out the values you need.