Search in Power BI Table Visual Using a Slicer For Contains Character Criteria

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.

The Challenge

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.

Visual Filtering

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:

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.