Slicer on comma separated list of string

[amandal1810]

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.

[ChristianR]

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

Thank you

[Ashish_Mathur]

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.

[v-kelly-msft]

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!

[Kartic21]

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

[Kartic21]

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?

[amandal1810]

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.

[v-kelly-msft]

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!

[FarhanAhmed]

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

[amandal1810]

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.

[FarhanAhmed]

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

[amandal1810]

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.