Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 (p...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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)

     

  • Icey's avatar
    Icey
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

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.