Forum Discussion

Budfudder's avatar
Budfudder
Helper IV
7 years ago
Solved

Reverse Contains?

I have a table containing listings of of our sales, and each row contains (among other data) the product(s) sold in that sale.   I have a slicer containing various types of products. The idea is th...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    7 years ago

    If you have a table that contains your product types (not related to your product table), then you can write a measure that checks if the product matches any of the selected ones:

     

    Visible = 
MAXX(
    VALUES(ProductTypes[Type]),
    MAXX(Products,
        IF(
            SEARCH([Type], Products[Product], 1, 0) > 0,
            1,
            0
        )
    )
)



    Then just set the visual level filter to only show [Visible] = 1.

Budfudder's avatar
Budfudder
Helper IV
7 years ago

Hi Alexis, it doesn't appear that your shorter version works...at least, it doesn't return the same result as the longer version. And (confirmed by checking with Advanced Find) the longer version is correct.

AlexisOlson's avatar
AlexisOlson
Super User
7 years ago

Interesting. I must not have been crazy to put it in there in the first place.

 

Is it different just for subtotals that represent multiple products or are there other cases as well?

  • Budfudder's avatar
    Budfudder
    Helper IV
    7 years ago

    Others as well - it just doesn't show all products matching the selection.

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      7 years ago

      That's odd. I'm not seeing any issues using the sample data you gave. Is there an example you can give where the two give different results?

      • Budfudder's avatar
        Budfudder
        Helper IV
        7 years ago

        I'm trying - first I need to understand both versions. In both the original and the newer version, you reference a field [Type] as the first argument in the SEARCH function. What field is that, and why didn't you have to qualify it?