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

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
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!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors