Have column with multiple values in powerBI, for use with a slicer

Question

I have a column with the following data

|  spoon

|  fork

|  fork & spoon

I would like to add a slicer to my page that has two options: Spoon, fork. When Spoon is selected it shows the result for the rows for Spoon and fork & spoon. When Fork is selected it shows the result for the rows for Fork and fork & spoon  

Can this be achieved in powerbi?  

If not directly, I was thinking that formatting my data like this could help perhaps? Does powerBI have an option to see values divided by semicolons as multiple values? 

|  Spoon

|  Fork

|  Fork ; Spoon

EDIT: Bit more info on my actual table layout:

| Service A |  revenue |  category 1    

| Service B |  revenue |  category 2

| Service C |  revenue |  category 1 & 2 

Answer 1

• It depends on the result we want to show for the filtered rows like sum, average, count, etc.

• This is an example of the filtered rows by using SUMX function available in DAX expressions. Let's say we have two tables like this:

• One is a table with data

• Another one is the table used for creating the slicer

• We need to add a slicer to out report using the SlicerTable[SlicerColumn] column. Then create a measure called Result in the MyTable table with the following expression:

Result =

SUMX (

    FILTER (

        MyTable,

        [MyColumn] = FIRSTNONBLANK ( SlicerTable[SlicerColumn], 0 )

            || FIND ( FIRSTNONBLANK ( SlicerTable[SlicerColumn], 0 ), [MyColumn], 1, -1 ) > -1

            || NOT ISFILTERED ( SlicerTable[SlicerColumn] )

    ),

    [Value]

)

• We do not need to get scared for this DAX expression. It basically checks if the column contains any of the values selected in the slicer, if there is no selection in the slicer it will sum up all values of [Value] column.

• Using a matrix or any Power BI visualization with the column and the Result measure, we will get something like this:

• When you add a slicer using CategorySlicer[Slicer] column, it will automatically filter the Service table because of the explicit relationships that exists between the underlying tables.