cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amandal1810
Microsoft
Microsoft

Slicer on comma separated list of string

Hi,

 

Here is what i am trying to achieve:

 

My dataset:

FieldNamePersonNameLovesPets
FieldDatatypestringstring
 Sheldonnull
 AmyDog
 PennyBird,Dog,Cat
 HowardCat,Turtle
 LeonardDog,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.

7 REPLIES 7
v-kelly-msft
Community Support
Community Support

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:

Screenshot 2020-10-05 100650.png

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:

Screenshot 2020-10-05 102921.png

For details,pls refer to attachment.

 

 

Best Regards,
Kelly

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

 

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.

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
Super User
Super User

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

 

Split-into-Rows.png







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

Proud to be a Super User!




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.

 

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

 

 







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

Proud to be a Super User!




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. 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors