Power pivot- counting text fields based on a condition

[masterelaichi]

Hi,

I am trying to find a way to count the number of occurrences of a specific string in a column in Power Pivot

I have a table with a column "Call type" with values “Incidents” and “Requests”. The table is around 1000 rows long with each call being either a Request or an Incident. Is there a way to count the number of occurrences of Incidents and Request?

Thanks

 

[scottsen]

Depending on how you want to use it, there are a few options.

A measure with just:
=COUNTROWS(MyTable)

Then drop call type on rows of your pivot table, and that measure on values... and there ya go?

 

[masterelaichi]

Hi,
Thanks for your response, but when I try doing this it returns the total number of rows. I should have framed my query more clearly. I am looking to get just the number of Incidents or the just the number of Requests
I am very new to powerpivot and hence unfamiliar with the formulas

Thanks

 

[masterelaichi]

I tried COUNTROWS(FILTER(Incident_Request,[Call Type]="Request")) and seems to do the trick. This was completely by trial and error. I still need to figure out how to use Calculate, Related and Filter functions K

 

[scottsen]

Do you have multiple tables? Otherwise Countrows(Incident_Request) should "just work" when you put Call Type on rows...

 

[masterelaichi]

Yes, I do have multiple tables. Does the above expression work only on text data types?