Forum Discussion
Reverse Contains?
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.
Others as well - it just doesn't show all products matching the selection.
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?
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?
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.
So the specification "VALUES('Product Types'[Type]" in the outer MAXX specifies the context for the inner MAXX as well?
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].
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)...
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])