How do you group a list and count each user on the list from 2 separate columns?

Hello

Just as an FYI, I cannot use LookML. I am forced to stick with custom filters/dimensions/measures/table calculations using table variables (ie ${customer.name}). Please do not post LookML code as I am not allowed/have access rights to LookML since it will affect all the reports that the entire company makes.

That being said, here is the issue. I have a table that table that looks like this, where a customer can use 2 different products simultaneously

Customer Primary Secondary
A prod1 prod2
B prod3 prod1
C prod3 prod2
D prod2 prod3
E prod4  

I would like to create a Look that will group the list of Products (i.e. Prod1, Prod2, Prod3, Prod4) and count how many customers use said product as their primary or secondary use. Ideally the results would look like this

The 2nd table shows the number of customers that uses each product as their primary or secondary. My problem is how do i get a count of customers who use the product as their secondary? It always results in 0, despite Prod2 having 2 customers that use it as their secondary. Any assistance would be greatly appreciated! Thank you!

1 REPLY 1

Not sure why the second table is no longer there, but there it is for an example

Product Customer - Primary use Count Customer - Secondary use count 
Prod1 1 1
Prod2 1 1
Prod3 2 1
Prod4 1 0

Is this possible to do without using LookML?

Top Labels in this Space
Top Solution Authors