Forum Discussion

JS00's avatar
JS00
Frequent Visitor
1 year ago
Solved

Return Products with Matching Characteristics Based on Input

Hello,

 

I have a product table as shown below

 

I want to create a report where a user enters a product name and selects certain slicers (as shown below), and the report will return matching products based on the selected criteria. For example, if a user enters 'Chewy-Dog Car Seat Cover' and selects the 'Price' and 'Origin' slicers, because the price of the 'Chewy-Dog Car Seat Cover' is $19.99, and its origin is the USA. If another product, like 'Petsmart - Dog Car Seat Cover,' has the same price and origin, it will be returned as a match. If the user enters 'Chewy-Dog Car Seat Cover' but only selects the 'Color' slicer, the report will return 'Fury-Dog Car Seat Cover' because they share the same color."

 

Thank you!

 

 

Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi JS00 

    Assuming that multiple matches are expected:

    Matching Product (multiple) = 
VAR _ProdName =
    SELECTEDVALUE ( 'Table'[Product Name] )
VAR _Origin =
    SELECTEDVALUE ( 'Table'[Origin] )
VAR _Price =
    SELECTEDVALUE ( 'Table'[Price] )
VAR _tbl =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Price] = _Price
            && 'Table'[Origin] = _Origin
            && 'Table'[Product Name] <> _ProdName
    )
RETURN
    CONCATENATEX ( _tbl, [Product Name], ", " )

     

    Single match:

    Matching Product (single) = 
VAR _ProdName =
    SELECTEDVALUE ( 'Table'[Product Name] )
VAR _Origin =
    SELECTEDVALUE ( 'Table'[Origin] )
VAR _Price =
    SELECTEDVALUE ( 'Table'[Price] )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Product Name] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Price] = _Price
                && 'Table'[Origin] = _Origin
                && 'Table'[Product Name] <> _ProdName
        )
    )

     

    Please see attached sample pbix.

2 Replies

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    1 year ago

    Hi JS00 ,

    As of now, there is no standard way to get user input like this. Alternate way is
    1. Use a slicer with search box enabled from top right three dot menu.

     


    2. Use Q&A visual to generate on demand report. As below

     

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi JS00 

    Assuming that multiple matches are expected:

    Matching Product (multiple) = 
VAR _ProdName =
    SELECTEDVALUE ( 'Table'[Product Name] )
VAR _Origin =
    SELECTEDVALUE ( 'Table'[Origin] )
VAR _Price =
    SELECTEDVALUE ( 'Table'[Price] )
VAR _tbl =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Price] = _Price
            && 'Table'[Origin] = _Origin
            && 'Table'[Product Name] <> _ProdName
    )
RETURN
    CONCATENATEX ( _tbl, [Product Name], ", " )

     

    Single match:

    Matching Product (single) = 
VAR _ProdName =
    SELECTEDVALUE ( 'Table'[Product Name] )
VAR _Origin =
    SELECTEDVALUE ( 'Table'[Origin] )
VAR _Price =
    SELECTEDVALUE ( 'Table'[Price] )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Product Name] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Price] = _Price
                && 'Table'[Origin] = _Origin
                && 'Table'[Product Name] <> _ProdName
        )
    )

     

    Please see attached sample pbix.