Interaction between elements of a column (count of possibilities)

[digodiano23]

Hi! My first question here!

I have a table with 2 coluns, PERSON and PRODUCT (A, B or C).

PERSON PRODUCT
Person1 A
Person1 B
Person2 A
Person2 C
Person3 A
Person4 B
Person5 A
Person5 B
Person5 C
Person6 B
Person7 C
Person8 A
Person9 B
Person9 C
Person10 C
Person11 C

I want to count how often product interactions occur.

The count of this example above will look like this:

Interaction Count
A           1 (only A)
AB          1 (only A and B)
ABC         1 (only A and B and C)
AC          1 (only A and C)
B           2 (only B)
BC          1 (only B and C)
C           3 (only C)

Could you help me with this solution?

Thank you very much friends

[lockematthewp]

I solved it but I had to create a few more fields (isA, isAB, etc.) I then used set analysis to count if isA = 1, … I am attaching an example to show you.

[digodiano23]

Thanks for the attention friend, but it doesn't solve my problem ...

I need the solution to have no duplicity of situations ... For example, Person1 can only be in "AB", not in other possibilities.

Imagine a Veen Diagram (using the table I showed above):

Thank you again!

[lockematthewp]

Problem solved. And in a much better way.

P.S. There were two people that had A only not one.

[digodiano23]

Great solution! But you did it "on load time".

I wanted to see if it is possible to do "at selection time", ie ... that this table would be assembled according to the application selections ... For example, if I select only products A and B, it would only bring A, B and AB ...

"On load time" would be a viable but not perfect solution for me ...

Even so, thank you very much for the idea!

PS: people that had A = 2 really... 😉

[lockematthewp]

Got it. 😁

I have two charts on top of each other with only one showing at a time. One for when no product selections are made and one where there was a selection. Try using the Product Listbox to choose. Let me know what you think.

[digodiano23 · Accepted solution]

Perfect Lockematthewp, good job!

But I could do it without using Load, directly in the dimension. Here's the solution (using your "concat" idea):

In dimension: "=Aggr( Concat(distinct PRODUCT,'') , PERSON)"

In expression: "=Count(DISTINCT PERSON)"

DimensionExpression

And with selection, we have this:

Once again, thank you very much for your help!