Forum Discussion
Solved
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 ...
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,
