Power BI - Problem, Design, Solution - Text Search Slicer

In this installment of the "Problem, Design, Solution" series, we are going to walk through the process of performing a text search using slicers in Power BI, simulating a “LIKE” type search. Please note that there is a video explaining the solution in-depth at the end of the blog under "Resources."

The following screenshot shows that when “Tax” is selected, all records in the table that have “Tax” anywhere in the record are returned. Likewise, whenever “IT” is selected from the slicer, all records in the table that have "IT" in them are returned. Hope you enjoy this post!

Problem

Normally slicers (visual filters) only filter records that are an exact match, for example, if you select “IT” from the slicer then you would only expect to see records that have "IT" and only "IT" in the record. If it does not match IT exactly, then those records are not returned. This is the intended behavior of a slicer and in general this is exactly what we want. However, there are times when you might want to do a “LIKE” type search, similar to the like function in T-SQL. In this scenario, we had a client who needed to solve this exact problem. The screenshot below is a simulated problem for this example.

In order to bring back all records that contain “IT,” you are required to click each occurrence of “IT” in your slicer. Of course, this isn’t a big problem if you have a small number of possible combinations, like our sample size, but in larger environments this may be an unreasonable expectation!

Design

Now that we have discussed both the problem and what we want the ultimate solution to look like, it is time to lay out the design. The design includes a four step process.

Let’s break this down a little bit:

• The first step in the process is to identify all the unique values that we need to filter on and put those values into their own distinct table. In this case, we are looking at departments (HR, IT, FIN, Audit, and Tax).
• The second step is to create a measure that uses the distinct departments in our new table as a filter. We will call this measure IsFiltered.
• The third step is to create a new slicer using the values listed in our disconnected table.
• The final step is to tie our new measure (IsFiltered) to the specific visual that we want to be affected by our disconnected table of distinct values.

Step 1 – Loading unique values in a distinct table

I would recommend storing this list of values in a file or in a table within a database. For this example, I will quickly put these values into a table in my PBIX file. First, I will navigate to the home ribbon and select “Enter Data." Then, I will name the column "Departments" and the table "Slicer." Next, I will add the five distinct departments as records for the departments column (IT, HR, Tax, FIN, Audit).

Note: This is using the disconnected table design. We are not going to set up a relationship between this new table and any other table in our model.

Step 2 – Creating the IsFiltered measure

This IsFiltered measure is going to give us the “LIKE” type search that we are looking for. Excited yet? Well you should be!

In this example, I am going to use the FIND function. The FIND function is exactly like the SEARCH function with the key difference being that the FIND function is case-sensitive and the SEARCH function is case-insensitive. The code below will complete step 2 in our design.

IsFiltered = 
IF(
    SUMX(‘Slicer’,
        FIND(
            ‘Slicer'[Departments],
            MAX(‘Department Goals'[Affected Departments]),,0)) > 0,
            “True“,
            “False“
)

What is this measure doing? The FIND function returns the starting position of the text string we pass in, if the value is greater than 0, that means that the text exists. This is how we generate a LIKE type search. If the text is not found, then nothing is returned and we give that record a default value of 0.

Step 3 – Create a slicer from the disconnected table

Now we can create a slicer from our distinct list of values. At this point, the slicer does not filter anything. This is because the filtering occurs through the measure we created in step 2 and needs to be added as a filter to a visual or report in our Power BI Report.

Step 4 – Use the IsFiltered measure to filter a visual in the report

The last step in this process is about as easy as step 3. First, we need to figure out which visuals we want to be filtered with the “LIKE” type operation and then we need to use the IsFiltered measure as a filter on those visuals. The criteria here will be to only show the records where the result is “True." Remember the DAX calculation from above? If a value was found, then a number higher than 0 is returned and we flag the record as “True."

Finally, I am going to add the IsFiltered measure as a filter to my table visualization. This is done in the Fields section of the visualizations pane. Refer to the image below:

Solution

With the 4-step design completed, the solution is also complete! Now we can apply a filter that performs a text search rather than an exact match, as seen in the image below:

Summary

By leveraging the DAX expression language, we are able to override the default behavior of slicers and filters in Power BI. Please note that this solution would also work in an SSAS Tabular model. The implementation would be slightly altered as you may be consuming that model from Excel rather than Power BI, but it would work nonetheless.

Resources

Video of solution:

Download the completed example here: http://tinyurl.com/yd2qon7x