Identify multiple values in one field

[asamariaekrickl]

Hi there,

The logic for my calculation is to count the IDs which have both Alert Code = 'ATSI' AND 'CITFU'

The below gives me the correct list of IDs if I have ID as dimension and the below as the calculation in a straight table.

SubStringCount(Concat(DISTINCT '|' & [Alert Code] & '|'), '|ATSI|') and  SubStringCount(Concat(DISTINCT '|' & [Alert Code] & '|'),'|CITFU|')

The expression value for each ID is -1.

My question is now how I turn this into a calculation to give me the count of these IDs.

I have tried this

if((SubStringCount(concat([Alert Code]), 'ATSI') and SubStringCount(concat([Alert Code]), 'CITFU')) < 0, count(distinct[Patient Ref No]))

This gives me a number much higher than the one that I'm after.

Any ideas how to solve this?

Thanks in advance

Asa

[tresesco · Accepted solution]

One solution could be like:

=Count(DISTINCT{<[Patient Ref No]={"=Count(Distinct {<[Alert Code]={'ATSI','CITFU'}>} [Alert Code])=2"}>}[Patient Ref No])

[asamariaekrickl]

Fantastic! Thank you soooo much!