How to do a unique count per single item?

[Anonymous]

Dear all,

I am seeking for a solution to get a unique count for the following situation:

I have a customer table with main customer number and a an additional customer delivery number:

Main customer number	Delivery customer number
111	123
111	134
111	145
222	234
222	245
333	345

The final result should be a measure showing for the following main customer number the following output:

111 ---> unique count of 3 delivery customer numbers

222 ---> unique count of 2 delivery customer numbers

333---> unique count of 1 delivery customer number

Any suggestion is welcome how to fix it.

Thanks!

[mangaus1111]

Like in this example

[Anonymous]

Thanks, but it still returns the total number of all rows back to each main customer number. Maybe I do something wrong.

[mangaus1111]

You need to create a measure, not a colum

[mangaus1111]

Hi @Anonymous ,

can you send me a screenshot of your wrong result?

[Anonymous]

Herewith a screenshot:

'Hoofd klantnummer' = Main customer number and 'Aantal afleverklantnummers' = Number delivery customer numbers'.

[Anonymous]

'Hoofd klantnummer' is soueced from table 'Dim Klant' en 'Aantal afleverklantnummers' is a measure written in the following way:

Aantal afleverklantnummers = COUNTROWS('Dim Afleverklant')

Table Dim Afleverklant is containing the delivery customer number

[mangaus1111]

Hi @Anonymous ,

do you have a 1 to many relationship between Dim Klant  and Dim Afleverklant?

[mangaus1111]

if you send me a sample file with no sensitive data I can try to give you further help

[Anonymous]

Herewith a link to the two tables Customer and Delivery customer

https://wetransfer.com/downloads/7c3eae73b96900346f55feefa3eff5b320221109080542/102c4d26efe073c29eb5...

[mangaus1111]

Hi @Anonymous ,

try to delete this row from your Delivery Table

[mangaus1111]

In my file the measure is working. If you want I can send you my file

[Anonymous]

Thanks. If you could send your file, I will appreciate.

[Anonymous]

@mangaus1111: yes, true, a one to many relationship (Dim Klant (1) to Dim Afleverklant (many).

[mangaus1111 · Accepted solution]

Hi @Anonymous ,

I think you can use simply the measure COUNTROWS('Table') and put it in matrix with the Main Customer Number column in the row.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

It works!