Slicer search-filter

Power BI
1

hello,
in the power bi report I have a slicer which allows me to search for a text…
there are alot of items in the slicer and each one has a checkbox next to it.
There is also a select all check box.
However, I now would like to filter the slicer by entering the text [iron]
This gives me alot of items which have the word iron in it.
so far so good.
Because there are so many of the search returned for iron, how can I have the select all checkbox for all the items of iron so that I do not have to select each one of them?
thank you

2

Hi @arkiboys,

Based on Data Mentor information:

Power BI currently does not provide a built-in feature to automatically “select all” items in a slicer based on a text search directly within the slicer itself. However, there are workarounds you can use to achieve this.

Steps to Automatically Select All Filtered Items in a Slicer

  1. Create a Supporting Column: Create a column that identifies rows that contain “iron”.
  2. Use a Dynamic Filtering Solution: Create measures to dynamically filter based on user input.
  3. Use these in your Report to facilitate automatic selection

Let’s break this down step-by-step.

Step 1: Create a Supporting Column

Create a new column in your data model that identifies rows containing the word “iron”.

  1. Go to the Modeling tab in Power BI Desktop.
  2. Select New Column, and create a new calculated column using a formula similar to the one below:
ContainsIron = IF(
    SEARCH("iron", Table[YourTextColumn], 1, 0) > 0,
    1,
    0
)

Copy

Code Explainer

Explanation: This DAX formula checks if the word “iron” is present in the YourTextColumn. If it is, the column ContainsIron gets a value of 1, otherwise 0.

Step 2: Use a Dynamic Filtering Solution

  1. Create a Parameter Table: Create a table that will house dynamically input text.
  2. Build a Measure: Use DAX to create a measure that filters based on the input text.
  • Create a new table by clicking Enter Data and name it ParameterTable with a single column called SearchText.
  • Create a new measure in your original table:
FilteredIronItems = 
CALCULATE(
    COUNTROWS(Table),
    FILTER(
        Table,
        CONTAINSSTRING(Table[YourTextColumn], SELECTEDVALUE(ParameterTable[SearchText]))
    )
)

Copy

Code Explainer

Step 3: Use these in your Report

  1. Create a Slicer for SearchText: Add a slicer to your report and set it to ParameterTable[SearchText].
  2. Use the FilteredIronItems Measure: Turn your visual to use this measure as a filter, and set the filter condition to FilteredIronItems > 0.

By using this workaround, when you enter “iron” in the search text slicer, the filtered measure will dynamically show rows containing “iron”, effectively acting like a “select all” for the condition.

DataMentor within the EDNA platform is a great resource to help you with possible solutions to your questions.

I hope this helps.

thanks
Keith

1 Like
3

@arkiboys,

You might also consider creating a classification table that groups similar items (e.g., all items containing “iron”) and linking this table to the dimension table driving your slicer. A slicer based on this new classification enables more efficient filtering based on your current dimension table. You could combine them, with your current dimension nested under the new classification dimension.

1 Like
5

Hi @arkiboys ,

You can check EnterpriseDNA Data mentor answer available at:

1 Like
6

thank you