Distinct count with distinct count as filter

[Laurix]

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!

[rsbin · Accepted solution]

@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,

[Kishore_KVN]

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!!

[vrvp]

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!

[rsbin · Accepted solution]

@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,

[vrvp]

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!

[rsbin · Accepted solution]

@Laurix ,

Please try this as your Measure:

OrderCount = CALCULATE( DISTINCTCOUNT( 'Beer&Liquor'[Order ID] ), 
                     FILTER( 'Beer&Liquor', 'Beer&Liquor'[NumberofCategories] = 1 ))

Regards,

[vrvp]

Nice, works well now. Many thanks!