Slicer where user can provided comma separated values

[Meena0155]

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 

[Meena0155]

Thanks lbendlin

Can you please elaborate on how can we add more than 2 conditions?

[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

[v-tsaipranay]

Hi @Meena0155 ,

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.

[Meena0155]

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

[v-tsaipranay]

Hi @Meena0155 ,

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.

[v-tsaipranay]

Hi @Meena0155 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @Meena0155 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[lbendlin]

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.