Intersection in Set expression which contains just two common fields .

[Ntiwary03]

I need to find the count of ID - where an ID has one necessary 'F2F' in channel and just one any other value. If it has more than two values apart from F2F, then that shouldnt be included. 

In this case my desired Output (Unique count of ID)  should be - '2' which is from B and C. A wouldnt be counted since it has more than one value apart from F2F - Phone and Email as well.

ID	Channel
A	F2F
A	Phone
A	Email
B	F2F
B	Phone
C	F2F
C	Email
D	F2F

Or

Please find the Venn diagram attached for the understanding of the problem statement. I need to find a colored area. If my interpretation is correct as per above statement

[petter]

The expression:

=If(Count(DISTINCT {<Channel={'F2F'}>+<Channel=-{'F2F'}>} Channel)=2,'F2F and only one other: ' & Concat(DISTINCT Channel , ' and '),0)

Could be used as a measure in a table where you have ID as the dimension and you will get:

ID  Channel
--|------------------------------------------------------
B | F2F and only one other: F2F and Phone
C | F2F and only one other: Email and F2F

The set expression   {<Channel={'F2F'}>+<Channel=-{'F2F'}>}    will select all ID's with F2F and any number of additional channels and making a count of them and checking if there are just two channels in total is the way to do it...

[Ntiwary03]

Thank you very much.

I understand this approach now, It is counting the channel against each ID in a table and giving the output only when there are two channels and one of them is F2F.

However, What I am also trying to achieve is the value count of ID (which is 2 in this case) in a KPI Object which I still am unable to do.

Thank you for your help. Much appreciated. 🙂