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