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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
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)

 

  • 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.