Forum Discussion
Distinct count with distinct count as filter
Hi,
I have a problem which should be simple, but I cannot get the correct result.
The source table is
| Order ID | Product | Product category |
| 1111 | Millers | Beer |
| 1111 | VAT69 | Liquor |
| 2222 | Bud | Beer |
| 3333 | Jack Daniels | Liquor |
| 4444 | Carlsberg | Beer |
| 4444 | Heineken | Beer |
| 4444 | Johhnie Walker | Liquor |
| 5555 | Ballentines | Liquor |
and as a result, I need to get the count of Order numbers having one Product category only:
| Product category | Order count |
| Beer | 1 |
| Liquor | 2 |
(Orders 1111 and 4444 should not be counted since they include both categories).
The real tables have more than two product categories.
Any ideas on how I can accomplish this?
Thank you!
Laurix ,
I would do this in two steps:
First create a new Calculated Column
NumberofCategories = CALCULATE( DISTINCTCOUNT( [Product category] ), ALLEXCEPT( 'Beer&Liquor','Beer&Liquor'[Order ID] ))Order IDProductProduct categoryNumberofCategories
1111 Millers Beer 2 1111 VAT69 Liquor 2 2222 Bud Beer 1 3333 Jack Daniels Liquor 1 4444 Carlsberg Beer 2 4444 Heineken Beer 2 4444 Johhnie Walker Liquor 2 5555 Ballentines Liquor 1 Second step is to create a new Measure:
OrderCount = CALCULATE( COUNT( 'Beer&Liquor'[NumberofCategories] ), FILTER( 'Beer&Liquor', 'Beer&Liquor'[NumberofCategories] = 1 ))There is probably a way to do this all in one step, but at least this will hopefully get you started.
Regards,
6 Replies
- rsbin
Community Champion
Laurix ,
I would do this in two steps:
First create a new Calculated Column
NumberofCategories = CALCULATE( DISTINCTCOUNT( [Product category] ), ALLEXCEPT( 'Beer&Liquor','Beer&Liquor'[Order ID] ))Order IDProductProduct categoryNumberofCategories
1111 Millers Beer 2 1111 VAT69 Liquor 2 2222 Bud Beer 1 3333 Jack Daniels Liquor 1 4444 Carlsberg Beer 2 4444 Heineken Beer 2 4444 Johhnie Walker Liquor 2 5555 Ballentines Liquor 1 Second step is to create a new Measure:
OrderCount = CALCULATE( COUNT( 'Beer&Liquor'[NumberofCategories] ), FILTER( 'Beer&Liquor', 'Beer&Liquor'[NumberofCategories] = 1 ))There is probably a way to do this all in one step, but at least this will hopefully get you started.
Regards,
Interesting solution. Thanks for sharing it!
However, for a particular case it does not work well. Please see below:
Order 6666 contains only Beer products and should be counted as 1, instead of 2.
How can this be corrected?
Thank you very much!
- Kishore_KVN
Solution Sage
Hello Laurix , I do have similar solution provided by rsbin .
Please look at it for your information:
Create two measure to get count and filter:
Measure one DAX looks like this:
Counting = CALCULATE(COUNT('Counting Data'[Order ID]),ALLEXCEPT('Counting Data','Counting Data'[Order ID]))Measure two DAX looks like this:
Final Count = CALCULATE('Counting Data'[Counting],ALLEXCEPT('Counting Data','Counting Data'[Product category]),FILTER('Counting Data','Counting Data'[Counting]=1))Output looks like this:
If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!
Thanks for providing this solution.
Same as for the other solution, it does not work well when there are only products of same category on an order.For this case, the solution proposed indicates for order 6666 2 products of same category, thus, ingoring them in the "Final count" measure.
The answer should be
Beer 2
Liquor 2How can this be adjusted to work for this case as well?
Many thanks!
