Count the number of rows containing specific text

[Anonymous]

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:

Any help would be appreciated.

[kcantor]

@Anonymous

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

[Anonymous]

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

[Anonymous]

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")

[Anonymous]

Hello,

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

[Anonymous · Accepted solution]

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.