Filter Table Visualization to Return All Valid Line Items

[Veritan]

Hi all,

I've tried searching around online and haven't been able to find an answer to this yet. My goal is to create a visualization (probably just a standard table), but I want to filter the output based on the value of any one item. Here is a sample of how my data looks (heavily truncated).

Orders

Order Number	Customer	Product	Qty Remaining
1	Test 1	A	0
1	Test 1	B	0
1	Test 1	C	10
2	Test 2	B	0
2	Test 2	C	0
2	Test 2	D	0
3	Test 3	C	10
3	Test 3	D	0
3	Test 3	E	20
4	Test 4	A	0
4	Test 4	B	0
4	Test 4	C	0
5	Test 5	B	10
5	Test 5	C	20
5	Test 5	D	30

Products

Product	Size	Color
A	Large	Red
B	Medium	Blue
C	Small	Green
D	Large	Yellow
E	Small	Orange

These are the 2 tables I am working with. They are related on the Product field. What I want to do is return all line items where at least 1 Qty Remaining is greater than 0 for any given Order Number. Done manually, the Orders Table would look like this when complete. Note that it includes all Order Numbers where at least one of the line items is greater than 0. But Order Numbers 2 & 4 are not included because all lines in there are 0. Does anyone have a DAX formula or something similar that would accomplish this? Thank you in advance!

Order Number	Customer	Product	Qty Remaining
1	Test 1	A	0
1	Test 1	B	0
1	Test 1	C	10
3	Test 3	C	10
3	Test 3	D	0
3	Test 3	E	20
5	Test 5	B	10
5	Test 5	C	20
5	Test 5	D	30

 

[alex78]

Hi,

with following M code ?

= Table.Combine(Table.SelectRows(Table.Group(Excel.CurrentWorkbook(){[Name="Orders"]}[Content], "Order Number", {"x", each _ }), each List.Sum ([x][Qty Remaining]) > 0)[x])

Regards

 

[Veritan]

Thanks Alex, that worked well! Really appreciate you taking the time to look at this.