Show orders with multiple product categories

[Anonymous]

Hello!

I have dataset with orders. Some orders have products from only one category, other orders have products from multiple categories. I'd like to show how many orders have products from multiple categories and how many orders have products from only one category. Additionaly I'd like to have possibility to see orders that contains selected category.

I tried with DISTINCTCOUNT and it showed orders with multiple categories but I couldn't filter by selected category. Is there any solution?

Example data table is below:

order	product	division
b1	12	a
b1	21	b
b1	22	b
b2	12	a
b2	21	b
b2	22	b
b2	32	c
b3	32	c
b3	32	c
b4	12	a
b4	32	c
b5	32	c
b6	21	b
b6	22	b

Thanks for any hints!

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

Try the following measure:

Measure = CALCULATE(DISTINCTCOUNT('Table'[order]),FILTER(ALLSELECTED('Table'),'Table'[product]=MAX('Table'[product])))

It will return what you want!

Wish it is helpful for you!

Best Regards

Lucien

[amitchandak]

@Anonymous , Plot this measure with product

countx(filter(summarize(Table, Table[product], "_1", distinctCOUNT(Table[division])), [_1]>1), [product])

Change the column in measure as per need

[Anonymous]

Thanks @amitchandak , unfortunately this measure gives no result, even on my sample data 😞