Identify multiple values in one field

asamariaekrickl — Thu, 02 Aug 2018 08:44:33 GMT

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.

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

Re: Identify multiple values in one field

tresB — Thu, 02 Aug 2018 09:07:15 GMT

One solution could be like:

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

asamariaekrickl — Thu, 02 Aug 2018 09:28:33 GMT

Fantastic! Thank you soooo much!