How do I count distinct values (words) across multiple columns.

[gobluemba]

Greetings,

I have columns with similar data (e.g. words), and I want to count the TOTAL number frequency of the words across all three columns. So if the world "airplane" occurs 5 times in column B, 6 times in column F, and 9 times in column G, I would get a Total count for the word "airplane" of: 20

Is there a formula I can use? 

Thanks in advance!

[tamerj1]

Hi @gobluemba 

let me present two options 

=
COUNTROWS (
FILTER (
'Table',
'Table'[Column B] = "airplane"
|| 'Table'[Column F] = "airplane"
|| 'Table'[Column G] = "airplane"
)
)

=
COUNTROWS (
FILTER (
'Table',
"airplane" IN { 'Table'[Column B], 'Table'[Column F], 'Table'[Column G] }
)
)

[gobluemba]

Thank you for the quick response @tamerj1 - I guess I wasn't clear, I apologize. I want to count every unique value in each of those three columns - airplane was an example - so I would also want to count "Car" and "train" and every other unique value. That make sense? 

[tamerj1 · Accepted solution]

@gobluemba 

If you don't have a list if the unique value you my create it as a calculated table 

List =
DISTINCT (
SELECTCOLUMNS (
UNION (
ALLNOBLANKROW ( 'Table'[Column B] ),
ALLNOBLANKROW ( 'Table'[Column F] ),
ALLNOBLANKROW ( 'Table'[Column G] )
),
"Value", [Column B]
)
)

make sure no relationships are created automatically. 
then you an place List[Value] in a table or chart visual along with the following measure 

Count =
SUMX (
VALUES ( List[Value] ),
COUNTROWS (
FILTER (
'Table',
List[Value] IN { 'Table'[Column B], 'Table'[Column F], 'Table'[Column G] }
)
)
)