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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.