Calculate DistinctCount

[milos90]

Hi -
I have an issue returning number of product names that appear more than once for one client. By itself this is an easy pull however I need to implement a third element - Client ID ( which appears for each client name reference - my issue)

Data elements: Client Name, ID, Product Name

What I need: Unique Product Name associated with each client name. So if there is a client name that has 2 or more product names - I want to see that. This is something that I was able to get as shown in the example
Example:

ClientN ProductN
21 LLC box
21 LLC ball


However the third component is creating issue for me because client ID shows up for each instance of client name and it is a different value. So once I plug in client ID ( naturally I will get more rows back which would neutralize my calculate(counta that worked just fine for the above example.
Example:
now because I m getting each Client ID my final results are not as accurate.
I would for example have 22 LLC show up twice just because it has 2 different client ID's but only 1 product name and my goal is to have their unique Cl ID
ClientN ProductN ID
22 LLC box M123
22 LLC box M124
In the first example this would not appear in my search criteria since client Name is associated with only 1 Product Name but here it shows up because of the addition of ID
So the number of columns is far greater that come back and including duplicates of product names

When I try doing calculate(distinctcount(product name),filter(client name)=earlier(client name) - it just runs the query but does not return anything. I have tried many other variations but no luck

Thank you - Milos

 

[milos90]

Also wanted to add that data is given in this way and I cant change that:

Client Name Client ID Product Name

BB8 1111 spherical ball
BB8 1110 spherical ball