Count number of string appearances

[#1]

Hi there,

I have a data set like below.

I would like to count the number of times that RED appears and use that as the expression on a Guage chart.

Can any one help?

Task ID	RAG Status
1	RED
2	GREEN
3	GREEN

[sunny_talwar]

Try this:

To count Red

=SubStringCount(Concat([RAG Status], '|'), 'RED')

To count Green

=SubStringCount(Concat([RAG Status], '|'), 'Green')

or

=Count({<[RAG Status] = {'RED'}>}[RAG Status])

=Count({<[RAG Status] = {'GREEN'}>}[RAG Status])

[#3]

Hi There,

This doesn't seem to have worked unfortunately, the count is showing as the total number of rows in the table, rather than the number of instances where the value is RED

Count({<[RAG Status] = {"RED"}>}[Rag Status])

Thanks

[sunny_talwar]

I guess would you be able to share a application where it isn't working?

[HirisH_V7]

Hi ,

Change your Expression like this,

May be its ,

Count({<[Rag Status] = {"RED"}>}[Rag Status])

OR

Count({<[RAG Status] = {"RED"}>}[RAG Status])

[#6]

Can you help me a little bit here too:

So I've just added an if condition.

I want to count the number of RAG status where the status is open.

This doesn't seem to work - to confirm, the count now does work. Just need the if to work around it.

if([Status]='Open', Count({<[RAG Status] = {"RED"}>}[RAG Status]), null())

[HirisH_V7 · Accepted solution]

Hi,

if([Status]='Open', Count({<[RAG Status] = {"RED"}>}[RAG Status]), null())

or

if([Status]='Open', Count({<[Status]={'Open'},[RAG Status] = {"RED"}>}[RAG Status]), null())

Will works Fine.In Your Table you should Suppress Null values at Presentation and Dimensions tab as well.

If your dimension is status means there you can see only Open as a field.

HTH,

Hirish