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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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.  

 

 

 

 

View solution in original post

5 REPLIES 5
kcantor
Community Champion
Community Champion

@Anonymous

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




Anonymous
Not applicable

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
Not applicable

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
Not applicable

Hello,

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

Anonymous
Not applicable

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.  

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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