If you have a lot of text in a table visual in Power BI and want to search to find all texts with a specific character in it, I have a solution for you. You can have an alphabet slicer and use it to filter the table visual, the trick is to combine it with a measure and use it as a parameter table. let’s see how the solution works.
I have a table for all customers, and I am showing them all in a table visual in Power BI. However, there are many customers in the list, let’s say 18K+. If I want to search for all customers who have “q” in the name, then I need to either scan the table myself, Or use a slicer with a search box, and search for character “q”, and then select all the names with “q” one by one! something like below is tedious!
This is not good, now let’s see what can be done.
Slicer with Contains Criteria Output
This is the sample output I am looking for; I select the character in the slicer, and then table just gets filtered and shows me all names with that character used in it:
This is great, isn’t it? Now that you can see what is expected and can be done, let’s see how you can do that.
Alphabet Table: Parameter Table
I started this by creating a parameter table for the Alphabet. The parameter table is a table that can be used to filter the result of visualizations, but not through the relationship, through some DAX measures. You can usually create a parameter table using What-If parameters in DAX if your parameter table consists of numeric values, However, in this case, our table includes list of characters, so we need to create that ourselves using a DAX expression:
Alphabet = SELECTCOLUMNS( GENERATESERIES(UNICODE('a'),UNICODE('z')), 'Character', UNICHAR([Value]) )
and this will give me a table with all the characters:
I have written a blog article last week and explained in detail how you can generate a list of characters using DAX GenerateSeries function If you want to learn how the expression above works, read my article about it here.
Make sure this table has NO relationship to your Customer table;
DAX Measure to Check Contains
Now the next step is to create a DAX measure that checks if the FullName column of the Customer table contains the selected character or not? The below simple measure can do that:
Exists = SEARCH( SELECTEDVALUE(Alphabet[Character]), SELECTEDVALUE(Customer[FullName]), ,-1)
I have used the Search DAX function here. The search function will return the character index in the text if it finds the value (starting from one), and as the last parameter I mentioned “-1” it will return -1 if it cannot find the character in the text. You can use FIND or other functions in DAX to achieve similar results. I have written a blog article explained about functions that you can use to search through a text in DAX, which you can read here to learn more.
Now you can create visualization like below, the slicer value is coming from the Alphabet table, and the table visual is from Customer table;
In the visual level filter of the table visual, Add the Exists measure (the measure we have created in the previous step), and set the filter to “is greater than or equal to” and type “1” in the text box, and apply filter.
That’s it, this will give you the result below:
I’m sure soon the Slicer in Power BI will have somehow a feature like this, but until then, this is a solution you can simply implement and use.
Download Sample Power BI File
Download the sample Power BI report here: