Forum Discussion
Filter Out All Rows Based On Criteria
Hello!
I have a dataset with the following three fields: Product, Warehouse, and Status. Below is an example of what this looks like.
| Product | Warehouse | Status |
| ABC | CA | 1 |
| ABC | NY | 2 |
| ABC | TX | 3 |
| JKL | CA | 1 |
| JKL | NY | 1 |
| JKL | TX | 2 |
| XYZ | CA | 1 |
| XYZ | NY | 1 |
| XYZ | TX | 1 |
Each product can have a different status at each warehouse. I would like to identify all products that are only in status '1'. Simply filtering on the report would give me this table:
| Product | Warehouse | Status |
| ABC | CA | 1 |
| JKL | CA | 1 |
| JKL | NY | 1 |
| XYZ | CA | 1 |
| XYZ | NY | 1 |
| XYZ | TX | 1 |
I don't want to see products 'ABC' or 'JKL' because they are a different status in the warehouses that were filtered out. I only want to see product 'XYZ' because it is only status '1' in every warehouse. Is this possible to filter like this?
Thank you!
Anonymous , Try this measure
Measure =
var _overall = countx(filter(allselected(Table), Table[Product] = max(Table[Product] ) ), Table[Product] )
var _st1= countx(filter(allselected(Table), Table[Product] = max(Table[Product] ) && [Status]= 1), Table[Product] )
return
if(_st1= _overall , Max(Table[Status]), Blank() )
1 Reply
Anonymous , Try this measure
Measure =
var _overall = countx(filter(allselected(Table), Table[Product] = max(Table[Product] ) ), Table[Product] )
var _st1= countx(filter(allselected(Table), Table[Product] = max(Table[Product] ) && [Status]= 1), Table[Product] )
return
if(_st1= _overall , Max(Table[Status]), Blank() )