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