Forum Discussion
Distinct count with distinct count as filter
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,
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!