Filter / Display rows based on substring present within a slicer

[vishal0soni]

I have a list of items (Apple, Bat, Cherry, Dog, and Elephant).

One or more of these items may or may not appear in the list of multiple comma-separated keywords (Apple, Cherry, Elephant).

What i need to do is to filter out the list of rows having only the selected keyword. So if I select the item "Apple", it should display only the rows having this keyword.

I am able to find out the rows having this item, but not able to display only the identified rows.Slicers for comma-separated values

Here, when i click "Apple" in slicer, it shall display only the rows having Apple keyword in the next table.

[vishal0soni · Accepted solution]

I think I got the easiest way of doing this just by using SEARCH function.

We can write this measure:

Exists = SEARCH(

    SELECTEDVALUE('Products'[Product]),

    SELECTEDVALUE(Solution_Products[SolutionProducts]),

    ,-1)

And then use this as a filter criterion for displaying the values. 

It works perfectly fine as desired. 

[vishal0soni · Accepted solution]

I think I got the easiest way of doing this just by using SEARCH function.

We can write this measure:

Exists = SEARCH(

    SELECTEDVALUE('Products'[Product]),

    SELECTEDVALUE(Solution_Products[SolutionProducts]),

    ,-1)

And then use this as a filter criterion for displaying the values. 

It works perfectly fine as desired. 

[Ritaf1983]

Hi @vishal0soni 

Please refer to the linked tutorial of @amitchandak :

https://www.youtube.com/watch?v=MKKWeOqFG4c

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[vishal0soni]

Thanks @Ritaf1983 

This looks good and promising. However, there is a challenge, the list of keywords is huge (hundreds of books), and each row has large number of keywords (all nouns in a book).  So making a duplicate column, and then normalizing that entire table would result in huge list I believe.

[Ritaf1983]

@vishal0soni Unfortunately I don't think we have another kind of solution, that's necessary to make the relationship...same logic as lookup.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[vishal0soni]

Can we make use of string functions like FIND or SEARCH dynamically to obtain the desired results on the fly?

[Ritaf1983]

@vishal0soni I don't see how it can work with the slicers...
but you can try to play with it...

[vishal0soni · Accepted solution]

@Ritaf1983 I got it. Used SEARCH function in a measure, and then used that in the filter.