Forum Discussion
Helper INeed a Count on Distinct Information
I am trying to create a Measure or a Column that will count the distinct information either based on an account number or first/last name. I tried COUNTROWS, I tired DISTINCT, and even 'Grouped By', but not having success. So either I am missing something or I don't know the easiest way to do this. Below is the data that I have ...
For those that are highlighted yellow, I would like the next column to contain a 2, 3 or 4. For those in white, those would only be 1.
Any assistance on how I can establish this information, would greatly be appreciated.
Thanks!
7 Replies
CA8172Someone had asked about the logic, but might have figured it out since it appeared to be deleted. However I will still respond.
And now looking at it, I forgot one column "Products". So when a customer is interested in a product, they request it. Which then creates a Case Number. That is the only unique key that is different for similar customers. So take for example Sam Spring, he has two cases, because he is interested/purchased two different products (ie: Sweater and Phone Charger).
I hope that clarifies the difference, since I forgot to include "Products" in the screen shot.
- Nathaniel_C
Community Champion
Hi CA8172 , try this:Measure = CALCULATE(COUNTROWS(myTable),ALLEXCEPT(myTable,myTable[Account Number]))
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel- CA8172
Nathaniel_C - I did as you suggested (see below), but it counted each row and is showing 21,912 for each Case Number, etc (see screen shot) when I try to put it all in a Table visualization.
PRODUCTS = CALCULATE(COUNTROWS(TableA),ALLEXCEPT(TableA,TableA[Account Number]))- Nathaniel_C
