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.
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.
Thank you, Alexis - genius! Now I've just got to work out why and how it works :)
Sorry, I should have explained it better.
Let's go from the inside out:
- The SEARCH function will return the position of the Type in the Product string if it finds it (returns 0 otherwise).
- If the position is greater than 0, then Type is a substring of Product and the IF function returns 1 (0 otherwise).
- The inner MAXX iterates through the list of products and the IF gives 1 for all the ones that match the current Type. (Within the filter context of the table, this list will only be the with the current product.)
- The outer MAXX is iterating through each product type that has been selected with the slicer and checks for each Type whether that string is a substring of Product using the inner MAXX. If any of the Types selected returns a 1, then the max over all the types is 1, just like OR logic.
If the inner MAXX part doesn't make complete sense, that's OK. I accidentally made it more complicated than it needed to be. Here's a slightly simpler version that's a bit easier to follow.
Visible = VAR CurrentProduct = SELECTEDVALUE(Products[Product]) RETURN MAXX(VALUES(ProductTypes[Type]), IF(SEARCH([Type], CurrentProduct, 1, 0) > 0, 1, 0))
Hi Alexis, it doesn't appear that your shorter version works...at least, it doesn't return the same result as the longer version. And (confirmed by checking with Advanced Find) the longer version is correct.
Interesting. I must not have been crazy to put it in there in the first place.
Is it different just for subtotals that represent multiple products or are there other cases as well?