Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
dgdgdg122db
Helper II
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 
111111.PNG
2 ACCEPTED SOLUTIONS
brigittagemes
Helper I
Helper I

Hi @dgdgdg122db ,

 

try DISTINCTCOUNT

 

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

 

use it in a measure.

 

Yours sincerely,

 

Brigi

View solution in original post

v-xicai
Community Support
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.

View solution in original post

2 REPLIES 2
v-xicai
Community Support
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.

brigittagemes
Helper I
Helper I

Hi @dgdgdg122db ,

 

try DISTINCTCOUNT

 

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

 

use it in a measure.

 

Yours sincerely,

 

Brigi

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.