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.

AlexisOlson's avatar
AlexisOlson
Super User
7 years ago

Yes. It's a nested context. In the inner MAXX, you can access both [Product] and [Type]. Within the outer MAXX, but not in the inner MAXX, you can only access [Type].

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])