Forum Discussion
How to filter an orders table by items
Hi, I have a single orders table with 3 fields:
Each row contains a single product, but of course, I can have the same order id with different products
| Document Number (order id) | UPC Code (product id) | Amount |
| 100034703 | 7630049202252 | 112 |
| 100034703 | 8058333491196 | 23 |
| 100034703 | 5058333491196 | 24 |
| 100034704 | 7630049202252 | 112 |
| 100034704 | 9058333491196 | 54 |
I need to create a slicer letting our sales to filter orders containing one or more products chosen but also showing the whole order content (other products ordered in the same order).
The problem is I'm not able to show the entire content of the orders but only the products selected in the slicer.
How can I do this?
Thank you!
You need a complex measure selector. It is sort of kind of like Inverse Selector: https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Selector/m-p/648290#M325
But, I created a sample file for you attached below that is specific to your case.
Create a disconnected table:
Codes = DISTINCT('Table'[UPC Code])Use that for your slicer and then this measure:Measure =VAR __Codes = VALUES('Codes'[UPC Code])VAR __Docs = SELECTCOLUMNS(FILTER(ALL('Table'),[UPC Code] IN __Codes),"__Document Number",[Document Number])RETURNIF(MAX([Document Number]) IN __Docs,1,0)Hi Anonymous ,
Just try to change the filter from '[Measure is 1' to '[Measure] is 0'.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- Greg_Deckler
Community Champion
You need a complex measure selector. It is sort of kind of like Inverse Selector: https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Selector/m-p/648290#M325
But, I created a sample file for you attached below that is specific to your case.
Create a disconnected table:
Codes = DISTINCT('Table'[UPC Code])Use that for your slicer and then this measure:Measure =VAR __Codes = VALUES('Codes'[UPC Code])VAR __Docs = SELECTCOLUMNS(FILTER(ALL('Table'),[UPC Code] IN __Codes),"__Document Number",[Document Number])RETURNIF(MAX([Document Number]) IN __Docs,1,0)- Icey
Community Support
Hi Anonymous ,
Just try to change the filter from '[Measure is 1' to '[Measure] is 0'.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.