Countx returns unexpected result

[dgdgdg122db]

Hi ,

I want to count No. of customers who occurred once and more than 1 once.

First I have a calculated column : 

Order occurrence = COUNTX(

FILTER('Unique Order customer',

EARLIER('Unique Order customer'[Customer No.])='Unique Order customer'[Customer No.]) ,

'Unique Order customer'[Customer No.])

For the occurrence =1, 

First Time Buyers = COUNTX(FILTER('Unique Order customer','Unique Order customer'[Order occurance]=1),'Unique Order customer'[Customer No.])

and this gives me the correct result.

However, when I switch to more than 1, the result is not what I want.

Returning Customers = COUNTX(FILTER('Unique Order customer','Unique Order customer'[Order occurance] >1),'Unique Order customer'[Customer No.])

as in the card visual, it shows 30, but it is actually only 8 customers 

[brigittagemes · Accepted solution]

Hi @dgdgdg122db ,

try DISTINCTCOUNT

https://docs.microsoft.com/en-us/dax/distinctcount-function-dax

use it in a measure.

Yours sincerely,

Brigi

[v-xicai · Accepted solution]

Hi @dgdgdg122db ,

As brigittagemes 's suggestion, you may create the [Order occurrence] using measure instead of calculated column like DAX below.

Order occurrence=DISTINCTCOUNT('Unique Order customer'[Customer No.])

Best Regards,

Amy

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

[brigittagemes · Accepted solution]

Hi @dgdgdg122db ,

try DISTINCTCOUNT

https://docs.microsoft.com/en-us/dax/distinctcount-function-dax

use it in a measure.

Yours sincerely,

Brigi