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,
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 2
How can this be adjusted to work for this case as well?
Many thanks!