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

When you operate with the row context of a table, the columns of that table aren't required to be fully qualified (just like when writing a calculated column). The MAXX function (as well as FILTER, MINX, SUMX, etc.) are iterator functions that iterate over a table going row by row doing calculations within the row context of that table.

 

The table VALUES(ProductTypes[Type]) is a single column table with column name [Type] that consists of the just the product types that are selected by the slicer. Since I'm using MAXX on this table, I can reference just by column name. That wouldn't work with MAX, which requires an explicit column reference.

Budfudder's avatar
Budfudder
Helper IV
7 years ago

So the specification "VALUES('Product Types'[Type]" in the outer MAXX specifies the context for the inner MAXX as well?