Need a Count on Distinct Information

[CA8172]

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!

[Nathaniel_C]

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]))

[Anonymous]

@CA8172 

It is filtered with account number, if you drag account number column to the table it should be clear.
If you want to countrows for each Case Number(Request Id) you need to change the "ALLEXCEPT" function to ALLEXCEPT(TableA,TableA[Case Number])).

Paul

[Nathaniel_C]

Hi @CA8172 ,
Would you share a picture of your data model relationships?

Thanks,

Nathaniel

[CA8172]

@Nathaniel_C- it is only one table so there is no relationships at this point

[Nathaniel_C]

Dummied PBIX 
Hi @CA8172 ,
So here is a pbix with dummied data, it is one table with a measure that is applied to a table. See if this gives you an idea.

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]

Someone 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.