topic Re: Slicer on comma separated list of string in Desktop

Slicer on comma separated list of string

amandal1810 — Wed, 30 Sep 2020 07:25:20 GMT

Hi,

Here is what i am trying to achieve:

My dataset:

FieldName	PersonName	LovesPets
FieldDatatype	string	string
	Sheldon	null
	Amy	Dog
	Penny	Bird,Dog,Cat
	Howard	Cat,Turtle
	Leonard	Dog,Cat

Now, i want to have a slicer (filter) with multi-select like so:

Pet:

Dog
Cat
Bird
Turtle
Blank

It would be great to have this list of values in the slicer created dynamically from the main dataset. But its okay if its not dynamic.

I tried doing this using Measure and SEARCH DAX query (found this approach online), but Measures are not applicable as a page level filter. So thats a deal breaker. Even when i apply this on a visual, the results were incorrect.

Any help appreciated.

Re: Slicer on comma separated list of string

FarhanAhmed — Wed, 30 Sep 2020 07:36:41 GMT

One way to do is you should split your column into rows in Power Query using "Split Column" and use that column as a slicer

Re: Slicer on comma separated list of string

amandal1810 — Thu, 01 Oct 2020 05:57:46 GMT

Thanks for your reply Farhan!

dont think this solution is viable. Reason: the concerned dataset is pulled from an Azure SQL Server and has over 4 million rows. Now if i split the column into rows like your said, its gonna explode the dataset. I dont think PowerBI will be able to handle it. Nevertheless, I will try out this approach.

Re: Slicer on comma separated list of string

FarhanAhmed — Thu, 01 Oct 2020 06:17:49 GMT

I am not sure if this works for you but have you checked Text Filter from App Source "?

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381309?src=office&tab=Overview

Re: Slicer on comma separated list of string

v-kelly-msft — Mon, 05 Oct 2020 02:31:01 GMT

Hi amandal1810 ,

The best way is to go to power query using split columns,if it's not a good solution for you,then you may create mulitiple columns using dax,such as below:

First create a column to count out the total number of comma:

Comma count = LEN('Table'[LovesPets])-LEN(SUBSTITUTE('Table'[LovesPets],",",""))

Then create a column to get the first column:

Column = var _searchcomma=SEARCH(",",'Table'[LovesPets],1,0) Return IF(_searchcomma=0,'Table'[LovesPets],LEFT('Table'[LovesPets],_searchcomma-1))

And you will see:

Then continue to create the remaining columns:

Remainingstring = IF('Table'[Comma count]=0,BLANK(),RIGHT('Table'[LovesPets],LEN('Table'[LovesPets])-LEN('Table'[Column])-1) )

Column 2 = var _searchcomma=SEARCH(",",'Table'[Remainingstring],1,0) Return IF('Table'[Remainingstring]<>BLANK(),IF(_searchcomma=0,'Table'[Remainingstring],LEFT('Table'[Remainingstring],_searchcomma-1)) )

Finally create a table for slicer:

Slicer table = DISTINCT(UNION(DISTINCT('Table'[Column]),DISTINCT('Table'[Column 2])))

And you will see:

For details,pls refer to attachment.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Re: Slicer on comma separated list of string

amandal1810 — Tue, 06 Oct 2020 05:09:36 GMT

yes, i did take a look at this. The end user has to type in the values, which is not what i am looking for.

Re: Slicer on comma separated list of string

amandal1810 — Tue, 06 Oct 2020 05:12:19 GMT

Thanks a lot Kelly for your detailed reply! Unfortunately it does not address my use case completely.

The number of values in the LovesPets column is dynamic: so i would not know for sure how many columns i need to split it across. And the RemainingString column would still contain the comma concat-ed strings, thereby landing me in the same problem that i started with.

Re: Slicer on comma separated list of string

v-kelly-msft — Wed, 07 Oct 2020 05:25:33 GMT

Hi amandal1810 ,

Yes,using dax is a bit complex in your case,as LovesPets column is dynamic ,we dont know how many comma each row may have,column "comma count" is help to count the total number of comma in a row,which is telling you how many columns you may need to create.

----RemainingString column would still contain the comma

Yes,as each row may have different numbers of comma,we need an intermediary column to store the remaining strings which may have comma inside.There's no easy way to directly get all the values before comma.

My suggestion is if possible,you'd better go to power query>split column by comma,which would be much easier.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Re: Slicer on comma separated list of string

ChristianR — Fri, 21 Jan 2022 19:44:47 GMT

Have you found a solution for this? I have the same issue and the proposed solution wouldn't work either.

Thank you

Re: Slicer on comma separated list of string

Ashish_Mathur — Fri, 21 Jan 2022 23:41:09 GMT

Hi,

First and foremost, the LovesPets column should have only one entry per cell. So please use the split cells by delimiter feature. Under Advanced, select split by rows there.

Re: Slicer on comma separated list of string

Kartic21 — Thu, 18 Apr 2024 19:19:58 GMT

HI Kelly ,
once the column are created and slicer table is created, what is be done after that ?
I dont see the slicer table filtering the data in table.

Can you please guide or suggest what i am missing after that?

Re: Slicer on comma separated list of string

Kartic21 — Thu, 18 Apr 2024 19:27:23 GMT

HI Kelly ,

I created the columns and slicer table as shown by you in steps but i dont see slicer able to filter the table ( in the PBI shared by you also it not able to filter )
Am i missing something , could you please guide with next steps

.. thanks