Power bi slicer contains with examples

In this power bi tutorial, we will see about the Power bi slicer contains. And also we will see the below points:

• How to create power bi slicer search contains
• Power bi slicer contains text

How to create power bi slicer search contains

Here we will see how to create a power bi slicer search that contains the character in the power bi desktop.

Scenario:

• In your power bi table visual (List of customer’s full name), if you have lot of text and you want to seach to find all text with a specific character in it.
• For this we will create a alphabets slicer and use it to filter the table visual.
• When you click on the alphabets/character in the power bi slicer and then table( full name) just get filtered and shows the text with that character used in it.

Follow the below steps to create a slicer with contains criteria output.

Step 1 Create a parameter table for Alphabets

• We will create a parameter table for the alphabets, the parameter tables is a table that can be used to filter the result of the visualization, by using Dax measure not through the relationship.
• In this case, the table includes list of character, so we will create using DAX expression.
• To create a new table, click on the Modelling tab -> New table from the ribbon.
• The DAX expression is:

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

• Select the slicer from the visualization.
• In the field, drag and drop the character field from the alphabet table in the field pane.

In the below screenshot you can see the power bi slicer contains the list of characters.

Step 2: DAX measure to check contains

• Now we will create a DAX measure to check if the FullName column of the DimCustomer table contains selected character or not in power bi desktop.
• In this DAX measure we will also use Search function, which will return the character index in the text if it finds the value starting from 1 and as last parameter it return -1, if it cannot find the character in the text.
• The DAX measure is:

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

• Make sure that the Alphabet table and Dimcustomer table have no relationship.

Step 3: Visual Filtering using Power bi slicer

• Now we will create a visualization and we have already created slicer visualization in Step 1.
• Next we will create a table visualization, for this select table visual from the visualization pane.
• In the value field, drag and drop the CustomerKey and full name columns from the DimCustomer table in the field pane.

• Select the table visual in the canvas and in filter pane, under filter on visual add the measure we have created in Step 2( Exists).
• Under show item when the value select is greater than or equal to and in below box write 1.
• Apply filter.

• Now select the slicer visual in the canvas in power bi, and the click on more icon(…) -> Search.

• Now you can search the character from the character list. Then by clicking on the character you can filter the fullname of the customer in the table visual.
• And the filtered result in the power bi table visual will shows the names with the character used in it.

Read Power BI Funnel Chart

Power bi slicer contains text

Here we will see how power bi slicer filters using text in power bi desktop.

• We are using the sample data that contains date and text, you can see in the below screenshot.

• We will create another table where we will add some words from these texts ( Table 1) .
• When we select a word( in slicer) it will fiter the visual and shows the text with the word used in it.

• Load the data to the power bi desktop
• Now we will create a measure that will search the word from the text, it will match then it will show the result.
• The measure is:

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

• Now we will create a visual, Select the table visual from the visualization pane.
• In the value field, drag and drop the date, text and measure from the field pane.

• Next, we will create a slicer, select the slicer from the visualization pane in power bi.
• In the field section, drag and drop the column1 from the field pane.

When you select the word in the slicer, it will filter the visual and show the text with the word used in it.

You may like the following Power Bi tutorials:

• Power bi slicer checkbox
• Power bi slicer multiple columns with examples
• Microsoft Power BI Card – How to use
• Power Bi slicer multiple selections
• How to set default value in Power BI Slicer
• How to create a Power BI Dashboard in Microsoft teams
• How to create a filter in Power bi

In this power bi tutorial, we learned about power bi slicer contains. And also we discuss the below points:

• Power bi slicer search contains
• Power bi slicer contains the text