cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Counting Text Values in Multiple Columns

Hello,

 

I am attempting to count a total of multiple different text values within multiple columns.  For example, if I have three columns, with three different options "Yes" "No" and "N/A" I would want to count how many of each are in each row.  See attached photo showing the result that I would like to reach through a calculated column.  Please let me know if there is a dax formula that can be used to get to solve this, or if there is a different unique workaround to get this accomplished.  

 

DetectNo, DetectYes, and DetectNA would be calculated columns to count the totals of the values from Column 1, 2, and 3 per row.

 

1.PNG

 

Thanks,

 

Nate

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Counting Text Values in Multiple Columns

Hi @npals,

 

Based on my test, we can create three measures and create a table visual to work around.

 

DetectN/A = var
nety = CALCULATE(COUNTA(Table1[Column1]),FILTER(ALL(Table1[Column1]),Table1[Column1]= "N/A"))+ CALCULATE(COUNTA(Table1[Column2]),FILTER(ALL(Table1[Column2]),Table1[Column2]= "N/A"))+ CALCULATE(COUNTA(Table1[Column3]),FILTER(ALL(Table1[Column3]),Table1[Column3]= "N/A"))
return
IF(ISBLANK(nety),0,nety)
DetectNo = var
nety = CALCULATE(COUNTA(Table1[Column1]),FILTER(ALL(Table1[Column1]),Table1[Column1]= "No"))+ CALCULATE(COUNTA(Table1[Column2]),FILTER(ALL(Table1[Column2]),Table1[Column2]= "No"))+ CALCULATE(COUNTA(Table1[Column3]),FILTER(ALL(Table1[Column3]),Table1[Column3]= "No"))
return
IF(ISBLANK(nety),0,nety)
 
DetectYes = var
nety = CALCULATE(COUNTA(Table1[Column1]),FILTER(ALL(Table1[Column1]),Table1[Column1]= "Yes"))+ CALCULATE(COUNTA(Table1[Column2]),FILTER(ALL(Table1[Column2]),Table1[Column2]= "Yes"))+ CALCULATE(COUNTA(Table1[Column3]),FILTER(ALL(Table1[Column3]),Table1[Column3]= "Yes"))
return
IF(ISBLANK(nety),0,nety)


Then we can get the result as below

 

image001.png

 

For more details, please check the pbix as attached.
https://www.dropbox.com/s/dmelerdhcw5dvfw/Counting%20Text%20Values%20in%20Multiple%20Columns.pbix?dl...

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Counting Text Values in Multiple Columns

Hi @npals,

 

Based on my test, we can create three measures and create a table visual to work around.

 

DetectN/A = var
nety = CALCULATE(COUNTA(Table1[Column1]),FILTER(ALL(Table1[Column1]),Table1[Column1]= "N/A"))+ CALCULATE(COUNTA(Table1[Column2]),FILTER(ALL(Table1[Column2]),Table1[Column2]= "N/A"))+ CALCULATE(COUNTA(Table1[Column3]),FILTER(ALL(Table1[Column3]),Table1[Column3]= "N/A"))
return
IF(ISBLANK(nety),0,nety)
DetectNo = var
nety = CALCULATE(COUNTA(Table1[Column1]),FILTER(ALL(Table1[Column1]),Table1[Column1]= "No"))+ CALCULATE(COUNTA(Table1[Column2]),FILTER(ALL(Table1[Column2]),Table1[Column2]= "No"))+ CALCULATE(COUNTA(Table1[Column3]),FILTER(ALL(Table1[Column3]),Table1[Column3]= "No"))
return
IF(ISBLANK(nety),0,nety)
 
DetectYes = var
nety = CALCULATE(COUNTA(Table1[Column1]),FILTER(ALL(Table1[Column1]),Table1[Column1]= "Yes"))+ CALCULATE(COUNTA(Table1[Column2]),FILTER(ALL(Table1[Column2]),Table1[Column2]= "Yes"))+ CALCULATE(COUNTA(Table1[Column3]),FILTER(ALL(Table1[Column3]),Table1[Column3]= "Yes"))
return
IF(ISBLANK(nety),0,nety)


Then we can get the result as below

 

image001.png

 

For more details, please check the pbix as attached.
https://www.dropbox.com/s/dmelerdhcw5dvfw/Counting%20Text%20Values%20in%20Multiple%20Columns.pbix?dl...

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Highlighted
Regular Visitor

Re: Counting Text Values in Multiple Columns

Hi @v-frfei-msft, Frank, I have similar situation here but I can't pass the columns to the formula since it changes every refresh. Imagine that sometimes I have 3 columns, just like the example, but sometimes more than 3 or less than 3... it happens every refresh of database and my dashboard should catch it. So, what I want need to do is count the occurrences of word in a row, the same what you did, but independent how many columns the dataframe has, got it? Perhaps there is a way to set a range of columns or concatenate all in one... I got stucked at this point and appreciate your help.

 

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors