How to filter an orders table by items

[Anonymous]

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!

[Greg_Deckler · Accepted solution]

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

RETURN

IF(MAX([Document Number]) IN __Docs,1,0)

[Greg_Deckler · Accepted solution]

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

RETURN

IF(MAX([Document Number]) IN __Docs,1,0)

[Anonymous]

Hi Greg, thank you for your support

Is it possible to do also the other way around? I'd like to exclude orders containing specific products

[Icey · Accepted solution]

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.