cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
Super User

@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 #])

Super User

@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 #])

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.