cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Calculate Number of Order Dates

AdventureWorks 2012 exampleAdventureWorks 2012 exampleWith AdventureWorks 2012, if you list customer name, order date and model number, the above customer data is listed with different order dates.  How do I count only once each unique order date, and then count the number of order dates per customer?

With the above example, Aaron coleman only ordered once and therefore the number of unique dates is ONE.  Aaron Collins also ordered more than one item, but ordered them on two unique dates and therefore the count for Aaron Collins is 2 order dates and so forth.

The answer should be as follows

Name                     Count of Order date

Aaron Coleman      1

Aaron Collins          2

Aaron Diaz              2

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support
Community Support

Hi @surfingjoe ,

 

DISTINCTCOUNT should be your best choice. To create a measure using it.
 
Measure = DISTINCTCOUNT('Table1'[Order date])
Capture.PNG
 
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Hi @surfingjoe ,

 

ALLEXCEPT should work here.

 

Measure = CALCULATE(DISTINCTCOUNT('Table1'[Order date]),ALLEXCEPT(Table1,Table1[Name]))

2.PNG

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
Community Support
Community Support

Hi @surfingjoe ,

 

DISTINCTCOUNT should be your best choice. To create a measure using it.
 
Measure = DISTINCTCOUNT('Table1'[Order date])
Capture.PNG
 
Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

In its simplest form that works.  I originally stated this doesn't work, but I was wrong to say that, it did not work for me but my issue was not in the same context as the example given.  After more experience with Power BI and going back and reading my example and your solution, I realized that of course your solution is the right answer for my example.  Thank You

Hi @surfingjoe ,

 

ALLEXCEPT should work here.

 

Measure = CALCULATE(DISTINCTCOUNT('Table1'[Order date]),ALLEXCEPT(Table1,Table1[Name]))

2.PNG

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors