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

Okay, I think I'm starting to get it. As an aside, how would you calculate the number of rows that meet the requirements the statement you provide is testing for? I need to give a count (I just found out)...

AlexisOlson's avatar
AlexisOlson
Super User
7 years ago

For that, you can just reuse the [Visible] measure.

 

If the Products table doesn't have any duplicate products in it, then you can simply write

 

Visible Count = SUMX(Products, [Visible])

Otherwise, you probably want

 

Visible Count = SUMX(DISTINCT(Products[Product]), [Visible])