Pbi slicer option

[Danielwood]

Hi all.

So I have a couple of tables in my PBI database, and I'm trying to get a slicer to work. Sounds simple enough, but where I am struggling is that the slicer needs to filter down where the selected option is contained within the field, but it only works where the text is at the end of the field.

For example the field has "apples, pears, bananas". If I select Apples or Pears in my slicer it shows nothing, but if I select Banana it shows.

From reading, it would appear that I could create another table that pulls in the relevant fields, then duplicate the rows to split out the apples pears and bananas. But honestly I have no idea how to do this in a way that keeps up to date from the source table.

[danextian]

Hi @Danielwood ,

The simplest solution is to split the column that contains the list of fruits into several rows and use that new column in a slicer.  It can be done via a measure as well but it is a more comlex approach. Please see attached sample pbix.

[dk_dk · Accepted solution]

Hi @Danielwood 

I hope I can provide a simple example you can follow for your usecase:

Let's say you have a table like this:

Where day is a day and fruits is the list of fruits you ate that day, separated with comma (actually in this case ", " - comma and a space. 

If I understand correctly, you want to be able to have a filter/slicer where you can choose one specific fruit, and list all of the days when that fruit was eaten.

In Power Query:

Right click on your table and click "Reference" (this will ensure that it will keep up to date with your source table)

Select the Fruits column and go to Transform> Split column > By delimiter. Set the delimiter to Comma, or --Custom--: ", " if you have space after your commas in your data. Click ok and all the fruits will be split into different columns.

Then right click on the Day column and select Unpivot other columns.

Remove the Attribute column, and rename the Value column to something that makes sense for you. This will be your filter.

Close and load.

In the model view, make sure that you connect the two tables based on Day (or whatever unique ID equivalent you have in your data)

The referenced table, which I called FruitsUnique should filter the original table (FruitsCombined).

You can build any visual you want using data from FruitsCombined, for simplicity I just make a table. The Fruit column (Value) from FruitsUnique can be used to filter it:

You will see that the rows show up if the selected fruit is present anywhere in the list in your original data.

Hope this helps! Let me know if you have any questions!

[Danielwood]

That has worked perfectly! Thank you so much!

[Ritaf1983]

Hi @Danielwood 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly