cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper II

## Countx returns unexpected result

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
2 ACCEPTED SOLUTIONS
Helper I

Hi @dgdgdg122db ,

try DISTINCTCOUNT

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

use it in a measure.

Yours sincerely,

Brigi

Community Support

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.

2 REPLIES 2
Community Support

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.

Helper I

Hi @dgdgdg122db ,

try DISTINCTCOUNT

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

use it in a measure.

Yours sincerely,

Brigi