cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bingo Regular Visitor
Regular Visitor

Count the number of rows containing specific text

Need help creating a basic filtered column. 

 

My goal is to create a column that counts all of the apples and another column that counts the total number of rows that do NOT contain the word "apple.".   Here's one example of the many statements I've attempted.

 

Apples Y = DIVIDE(COUNT(Food[Fruit]),CALCULATE(COUNT(Food[Fruit]),ALL(Food)))

 

I also tried to simplify and I can't get that working either.  The following statement counts each row 3 times.

 

Apples M = CALCULATE(COUNTROWS('food'), 'Food'[Fruit] = "Apple")

 

Here's the data table:

 

Food Table.JPG

 

Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Bingo Regular Visitor
Regular Visitor

Re: Count the number of rows containing specific text

The answer to this is both of my original statements were actually correct.  What I found was Power BI wasn't providing the correct answer unless I waited about 3 or 4 hours before it mysteriously started to show the correct answer.  After that, it seemed to provide the correct answer with every change I made to the page.   I was able to repeat this again later in a new Measure on a different Page.  

 

 

 

 

5 REPLIES 5
Super User
Super User

Re: Count the number of rows containing specific text

@Bingo

Why would you want that to be a column instead of a measure?



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Bingo Regular Visitor
Regular Visitor

Re: Count the number of rows containing specific text

Actually, I've tried both.  I was just trying to find a way to make it work and I did get it to work with my dummy table but not my actual table using a measure.  Here's what I got using a measure with my dummy table:

 

 

This statement produces a result of 5 apples (which is correct)

M Apples = CALCULATE(COUNTROWS(Food), Food[Fruit] = "Apple")

 

This statement products a result of 140 apples (which is NOT correct).  

Apples M = CALCULATE(COUNTROWS('food'), 'Food'[Fruit] = "Apple")

 

 

BTW: There are 28 rows and 5 apples (5x28=140).  I'm not sure what's causing that result but at least there's some logic.

 

I'd like to know the correct statement to exclude apples from the calculation.  That's my next problem (besides understanding why this won't work with my actual data).

 

Thanks

 

Bingo Regular Visitor
Regular Visitor

Re: Count the number of rows containing specific text

The answer to this is both of my original statements were actually correct.  What I found was Power BI wasn't providing the correct answer unless I waited about 3 or 4 hours before it mysteriously started to show the correct answer.  After that, it seemed to provide the correct answer with every change I made to the page.   I was able to repeat this again later in a new Measure on a different Page.  

 

 

 

 

fdelcorno Frequent Visitor
Frequent Visitor

Re: Count the number of rows containing specific text

THANKS - the below calculation worked for me (see my personal example in bold).

 

M Apples = CALCULATE(COUNTROWS(Food), Food[Fruit] = "Apple")

 

 

Active user count = CALCULATE(COUNTROWS('ACTIVE USERS'),'ACTIVE USERS'[Active?] = "true")

Bingo Regular Visitor
Regular Visitor

Re: Count the number of rows containing specific text

Hello,

That's great.  I was stuck for a while trying to get this to work.  I'm glad this post was helpful.