Measure that searches for any number of selected substrings

[Anonymous]

Currently I have a two tables, Sales and Keywords

Sales Table

Id | Value | Keywords

-------------------------------

0 |  100    | Fish, Meat, Dairy

0 |  50      | Fish, Fresh

0 |  25      | Fresh, Frozen

Keywords Table

Keyword |

----------------

Fish

Fresh

Meat

Dairy

Frozen

Id like display sales depending on a selected, or several selected keywords

So with a filter visual if i select Frozen and Fresh i would like to see these sales:

0 |  50      | Fish, Fresh

0 |  25      | Fresh, Frozen

As they each have at least one match.

Currently I have implemented a measure which allows me to do this for a single selected value:

Measure = 
    SEARCH (
        SELECTEDVALUE('Keywords'[Keyword]),
        SELECTEDVALUE('Sales'[Keywords]),
        ,
        -1
    )

I can then filter by the result of this measure being > 0 and will receive the results I desire. When multiple values are selected in the filter visual this no longer works(because of SELECTEDVALUE?)

[amitchandak · Accepted solution]

@Anonymous , Take out the keyword column from the first table. In power query create a new table using that table.

refer if needed

https://www.youtube.com/watch?v=kU2M1LmNvNo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=2

https://www.youtube.com/watch?v=vHuhbvYCiNc&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=3

Then duplicate the column and split the second column based on comma and then it will combination of column and each keyword. Join the new table with the existing table using many to many bidirectional and use that

[Anonymous]

Thanks! this works perfectly.

Is there a way to solve this with measures though? Afaik it is always preferred to use a measure vs creating extra columns / tables.