Forum Discussion
Slicer where user can provided comma separated values
Hi there,
I have a requirement for a slicer where the user can type in comma seperated values and the slicer will select all values that matches words in the comma seperated list. For eg, lets say we have a field called domain with the following values- legal cases, legal studies, legal consulting, finance, finance models, hr, transformation etc.
Now if the user types in the following text to the slicer - "legal, finance", then the reports should show data associated with the following domains
- legal cases,
- legal studies,
- legal consulting,
- finance,
- finance models
as each of then contains the word - legal or finance.
I considered using text filter, but this does not support comma separated values.
I know if we provided a search button for the usual slicer filter and type , say for eg, "legal", then the slicer will refresh to show all values with legal in it but the issues is that we may have 100s of values with legal and selecting them one by one is cumbersome and prone to errors.
Any help is appreciated.
Regards,
Meena
cc: Ritaf1983 , rajendraongole1
Hi Anonymous ,
Thankyou for the reply regarding your query confirmation.
The feature you are looking for is not directly available, but there is a workaround to achieve your objective.
Go through the below link so that you can get an idea.
Solved: Slicer capabilities with multiple text items in co... - Microsoft Fabric Community
As mentioned in the link, you can create custom text filters to refine the slicers. While the link shows how to use one text filter, you can easily create two or three text filters to suit your specific needs.I hope my suggestions give you good ideas, If you have any more questions or need further assistance, feel free to let me know.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
9 Replies
- lbendlin
Super User
Teach your users how to use the filter pane - there you can specify multiple filters like
contains "legal" or contains "finance"
(it is even possible to do more than two or conditions, with a little trick.
Hi Anonymous ,
Thank you for reaching out to Fabric Community Forum about how to improve slicer functionality in Power BI to support comma separated values.
To achieve the desired functionality where a user can input terms like "legal, finance" and have the report display all related domains, you can follow these steps:
- Open Power BI Desktop and navigate to Power Query Editor via Home > Transform Data.
- Create a new query: Home > New Source > Blank Query. Use the following M code to simulate user input (e.g., "legal, finance"):
let
KeywordTable = #table({"Keyword"}, {{"legal"}, {"finance"}})
in
KeywordTable
- Rename the query to “Keyword” and load it.
- Load your domain data (from an Excel file, SQL database, etc.) into Power BI. Assume it's stored in a table named Domain.
- Select the Domain table in Power Query and add a Custom Column with the following formula to check if the Domain includes any keywords:
= Table.AddColumn(#"Changed Type1", "Custom", each Table.AddColumn(#"Changed Type1", "Custom", each Table.AddColumn(#"Changed Type1", "Custom", each List.AnyTrue(List.Transform(Keyword[Keyword], (keyword) => Text.Contains(Text.Lower([Domain]), Text.Lower(keyword)))), type logical)))
- Filter the KeywordMatch column to display only rows marked as True
- Close the Power Query Editor and load the filtered data back into Power BI
I am also including pbix file for your better understanding. Hope this helps you to solve the issue, If you need further assistance, feel free to let me know.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Thank you v-tsaipranay
Unfortunatley, we dont have a fixed list of keywords that the user want to select by. So creating a table with keywords would not be very ideal.
But thanks for the response.
Cheers,
Meena
Hi Anonymous ,
Thankyou for the reply regarding your query confirmation.
The feature you are looking for is not directly available, but there is a workaround to achieve your objective.
Go through the below link so that you can get an idea.
Solved: Slicer capabilities with multiple text items in co... - Microsoft Fabric Community
As mentioned in the link, you can create custom text filters to refine the slicers. While the link shows how to use one text filter, you can easily create two or three text filters to suit your specific needs.I hope my suggestions give you good ideas, If you have any more questions or need further assistance, feel free to let me know.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
- lbendlin
Set the filter to Basic
Select more than two values from the dropdown
Set the filter to Advanced
Change all comparisons from "is" to "contains"
Publish the report
