Using a Slicer to Filter Concatenated Column

Working with concatenated columns (e.g., "India,USA,Japan") in Power BI can be challenging when you need to filter data based on individual values within those strings. Fortunately, slicers can be configured to dynamically filter such columns, allowing users to select specific entries (like "USA") and see only the rows where that value appears in the concatenated text. This technique is especially useful for dashboards dealing with multi-value fields, such as tags, categories, or locations.

In this exercise, we will create a DAX measure to filter the data based on the selected values in the slicer.

Let’s we have a table Locations in the Power BI, which we are going to use as a slicer to filter another table.

We have another table TaggedLocationSheet which we want to filter.

We have not created the relationship between the table.

Then create a measure with name FilterMeasure and use it on the table visual filter, set the filter to show items when value is 1.

DAX

FilterMeasure = 
VAR SelectedValues = VALUES(Locations[Countries])  
// Get all slicer selections

VAR CurrentRowValue = SELECTEDVALUE(TaggedLocationSheet[Tagged Locations])  
// Get current row's concatenated string

RETURN
IF(
    ISFILTERED(Locations[Countries]),  // Check if slicer is active
    IF(
        COUNTROWS(
            FILTER(
                SelectedValues,
                CONTAINSSTRING(CurrentRowValue, Locations[Countries])
            )
        ) > 0,
        1,  // Show row if it matches ANY selected value
        0   // Hide row if no match
    ),
    1  // Show all rows if nothing is selected
)    

How the Measure Works:

1. Capture Slicer Selections:
   • SelectedValues = VALUES(Locations[Countries])
   • returns all countries selected in the slicer (e.g., ["India", "China"]).
2. Get Current Row's Locations:
   • CurrentRowValue = SELECTEDVALUE(TaggedLocationSheet[Tagged Locations])
   • retrieves the concatenated string for the row being evaluated (e.g., "India,America").
3. Check Slicer State:
   • ISFILTERED(Locations[Countries])
   • ensures the logic runs only when the slicer is used.
4. Match Detection:
   • FILTER(SelectedValues, CONTAINSSTRING(CurrentRowValue, Locations[Countries]))
   • iterates through each selected country and checks if it exists in Tagged Locations. For example, if Tagged Locations = "India,America" and slicer selections are ["India"], it returns ["India"] (a match).
5. Final Decision:
   • If any match is found (COUNTROWS > 0), return 1 (show row); otherwise, return 0 (hide row).
   • If no country is selected, return 1 (show all rows).

Example Scenarios:

Let’s add the measure in the visual level filter of the visual which we want to be filtered.

When we have not selected any value from the slicer, we can all the values in the table visual as shown in the image below:

When we select the value in the slicer, we can see that the table visual is filtered, as shown in the image below:

We can select multiple values in the slicer, and their corresponding values are filtered in the table visual as shown in the image below:

Note: The CONTAINSSTRING DAX function is case-insensitive, so it performs case-insensitive matching.