How to find distinct items where all detail meets specific criteria?

[StevenHiatt]

I have a set of data of sales orders. Each order may contain several items. Some items are listed on our website and others are not. I am trying to find the number of orders where ALL of the items on that order are web items?

Sample data:

Order#		Item#		Web Item?
Order1		Item1		1
Order1		Item2		0
Order2		Item3		1
Order3		Item1		1
Order3		Item4		1
Order3		Item5		1

In the data above, Orders 2 & 3 have all web items. How do I write a DAX formula to figure that out? (I've tried variations of DISTINCTCOUNT and SUMMARIZECOLUMNS but can't wrap my head around how to get it to work right. In Excel I can do a SUMIFS but that doesn't translate over to DAX.)

Thanks.

[amitchandak · Accepted solution]

@StevenHiatt , Create a measure like below and plot with order # in visual or use as total

countx(filter(summarize(Table, Table[order#], "_1", count(Table[Item #]), "_2", calculate(count(Table[Item#]) filter(Table, Table[Web Item]=1))),
[_1] =[_2]), [order #])