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 semicollons 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

I think you can, but not sure as its been some time since I used it. There is an option that lets you import the data based on the delimiter. Can you check that? — Dinesh.hmn

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-02-23T03:52:22

It can be achieved depending on the result you want to show for the filtered rows, e.g, a sum, average, count, etc.

I will show you a very simple example of a sum for the filtered rows by using the SUMX function available in DAX expressions.

Suppose you have two tables like this:

MyTable: Table with the data.

SlicerTable: The table used for creating the slicer.

Add a slicer to your 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]
)

Don't get scared for this DAX expression, it bassicaly check if the MyColumn 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 MyColumn column and the Result measure, you will get something like this:

UPDATE Alternative support for slicer without a required measure using explicit relationships and adding tables.

I've added two additional tables to your model in order to support your slice requeriment. You will have to create an additional table for supporting many to many relationship and other to get the unique category values.

Data

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.

If you get stuck creating the required tables, there is a couple of DAX expressions I have in mind to create them.

Also be sure the Cross Filter Direction relationship property is set to Both in every relationship.

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

1 Answers