Counting if .... is greater than AND...

[Anonymous]

Hi All,

Here's my data:

Employee	Call Type	Phone Number
John	Work	07100 123456
John	Personal	07100 123456
John	Work	07999 654321
Sarah	Work	07123 456789
Sarah	Work	07456 567890
Sarah	Personal	07000 787878
John	Work	07100 123456

The end result will look like this:

So what I'm trying to calculate is:

show me the phone number that has been used for both work and personal calls (and only these numbers)

I tried something like this as the measure function in a bar chart count(call type = 'work'>0 AND call type = 'Personal'>0) - obviously this doesn't work - please can someone provide a formula to do this??

thanks,

Pokes

[Anonymous]

Hi Damian,

Yeah it looks like a good solution, unfortunately its showing an 'error in expression' alert - any ideas?

cheers,

Pokes

[Anonymous]

Damian,

I'm using the formula exactly as suggested by Stefan...

Count({<PhoneNumber = {"=Count(DISTINCT CallType)=2"}>} PhoneNumber)

not too familiar with set analysis so not sure how / what to amend.

cheers,

Pokes

[swuehl]

Double check that field names are spelled correctly (and take care of upper / lower case).

[#5 · Accepted solution]

Working
COUNT({<[Phone Number]={"=COUNT(DISTINCT([Call Type]))=2"}>}[Phone Number])