Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
With 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
Solved! Go to Solution.
Hi @surfingjoe ,
Measure = DISTINCTCOUNT('Table1'[Order date])
Hi @surfingjoe ,
ALLEXCEPT should work here.
Measure = CALCULATE(DISTINCTCOUNT('Table1'[Order date]),ALLEXCEPT(Table1,Table1[Name]))
Hi @surfingjoe ,
Measure = DISTINCTCOUNT('Table1'[Order date])
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |