How to use Contains in Power BI Slicer Visual?

Recently, I have been working with the slicer visual in Power BI. A client requested a system to track team performance using various visualizations based on employee data.

The client asked us to filter and display employee data whose designation contains the Manager role.

In this Power BI tutorial, let’s see how to use contains in a Power BI slicer visual. Here, I will cover the following topics:

• Filter Data Contains Text using the Filter Pane in Power BI
• Search Data Contains a Specific Character in Power BI Slicer
• Use a DAX Measure Power BI Slicer to Filter Data that Contains Text

Power BI Filter Contains Text using Filter Pane

Here, let’s see how to filter the data in a Power BI table visual that contains a specific text using the filter pane.

Below, you can see that I have created a Table visual that consists of Employee Details with different columns.

I wanted to filter the table data based on Employee Designation that contains specific text, i.e., Manager.

You can do this by using the Filters Pane in Power BI.

1. Launch the Power BI Desktop app -> click on the ‘Get data’ button from the ribbon menu. Select the data source from the list of options. Here, I have connected to an Excel data source.

2. Then, you’ll see the window below. Select the checkbox of the Sheet. Click the Load button to load the data into the Power BI report view.

Now the data will be loaded and the Excel columns will be displayed under the Data pane.

3. Now, navigate to the Filters pane. Under Filter on this visual, drag and drop the ‘Designation‘ field into Add data fields here. Choose the parameters below and click on Apply filter.
   • Filter type – Advanced filtering
   • Show items when the value is – Contains
   • Enter the value as ‘Manager’

Then, you can see the Table visual will be filtered with the data that contains a specific text.

Read Add Slicer Checkbox in Power BI

Search Data that Contains a Specific Character in Power BI Slicer

Now, let me show you how to create a slicer that will filter the data that contains a specific character in Power BI.

Assume I have a Table visual containing a list of Employee Names on my Power BI report.

Requirement:

From the table list values, I wanted to search and filter the Employees whose names contain a specific character by using the Power BI slicer.

For this, we will create an alphabet slicer and use it to filter the table visual. Let’s create a measure that creates a table of the alphabet.

Check out the steps mentioned below:

1. Launch the Power BI Desktop app. On the Power BI report view, click on the Modelling tab -> New table from the ribbon.

Alphabet = 
SELECTCOLUMNS(
GENERATESERIES(UNICODE("a"),UNICODE("z")),
"Character",
UNICHAR([Value])
)

Now, the table has been created. Let’s use the Alphabet characters in a slicer visual.

2. Select the slicer visual from the visualizations pane. In the field, drag and drop the Character field from the alphabet table in the field pane.

3. In the screenshot below, you can see that the Power BI slicer contains the list of characters.

Up to this point, the Table visual and the slicer visual are ready in the Power BI report view. Now, we need to create a DAX measure that checks if the Employee’s name contains a specific character in the Power BI Table Visual.

4. On the Power BI report view, click on Modeling and select New Measure.

This measure returns the character index of the text if it finds the value starting from 1. If it does not find the character in a text, it returns -1 as the last parameter.

Exists = SEARCH(
    SELECTEDVALUE(Alphabet[Character]),
    SELECTEDVALUE(DimCustomer[FullName]),
    ,-1)

Note:

Make sure that the Alphabet table and Employee Details table have no relationship.

5. Next, select the table visual in the canvas. In the filter pane, under the “Filter on Visual” section, add the measure we created (Exists).
6. Under the Show item, when the value selected is greater than or equal to, and in the box below, write 1. Click on Apply filter.

7. Now, select the slicer visual in the canvas in Power BI, and click on the more icon (…) -> Search.

8. Now you can search for the character from the character list in the search box. Then, by clicking on the character, you can filter the Name in the Employee details table visual. The filtered result in the Power BI table visual will display names containing the specified character.

See Power BI SUM Measure

Filter Data by Power BI Slicer Contains Text

Here, I will show you how to filter the data that contains a specific text using the Power BI slicer.

1. As shown in the screenshot below, the sample data contains a Date and Text column.

2. Then, I will create another table to add words from these texts (Table 1).

3. Load the data into the Power BI Desktop. Next, we will create a measure that searches for the word in the Text column, matches it, and displays the result.

Measure = VAR searchvalue=search(SELECTEDVALUE(Text_Queries[Column1]),SELECTEDVALUE(Table1[Text]),,Blank())
RETURN
If(searchvalue>0,"Found")

4. On the Power BI report view, select the Table visual from the visualizations pane. In the value field, drag and drop the Date, Text, and Measure from the field pane.

5. Next, create a slicer in the Power BI report. Select ‘Slicer‘ from the visualization pane. In the field section, drag and drop column 1 from the field pane.

Selecting the word in the slicer filters the visual and displays the text containing the word.

Although the slicer is used to filter data in the Power BI report page by column values, you can also create measures or use the filters pane to filter data that contains a specific text or character. If you have any suggestions or additional tips, feel free to share them in the comments below.

You may like the following Power BI tutorials:

• Power BI Slicer Filter Another Slicer
• Power BI Treemaps
• Create Date Range Slicer in Power BI
• Add and Customize Button Slicer in Power BI

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.