Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
gobluemba
Helper I
Helper I

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

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!

1 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] }
)
)
)

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

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] }
)
)

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? 

@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] }
)
)
)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.