Create a filter from multiple columns in Power BI

Let's assume that you have the following table 'Cities' in Power BI:

ascii
|         | Barcelona | Bangkok | Madrid |
|---------|-----------|---------|--------|
| Andrea  |     X     |         |        |
| Erik    |     X     |    X    |   X    |
| Jaruwan |     X     |    X    |   X    |
| Elena   |     X     |         |   X    |

You want to create a unique slicer to filter by the city using multiple columns:

ascii
☐ Barcelona
☐ Bangkok
☐ Madrid

First of all, note that having three different columns would imply creating separate slicers. However, it is possible to achieve this with just one slicer.

Begin by transforming the table (you can do this by replacing 'X' with each of the columns):

ascii
|         | Barcelona | Bangkok | Madrid |
|---------|-----------|---------|--------|
| Andrea  | Barcelona |         |        |
| Erik    | Barcelona | Bangkok | Madrid |
| Jaruwan | Barcelona | Bangkok | Madrid |
| Elena   | Barcelona |         | Madrid |

Now add a column that is the combination of the three cities:

Power
Concat = 
let
    values = List.RemoveNulls({[Barcelona], [Bangkok], [Madrid]}),
    concatenated = Text.Combine(values, ", ")
in
    concatenated

This would be the result:

ascii
|         | Barcelona | Bangkok | Madrid  | Concat                          |
|---------|-----------|---------|---------|---------------------------------|
| Andrea  | Barcelona |         |         | Barcelona                       |
| Erik    | Barcelona | Bangkok | Madrid  | Barcelona, Bangkok, Madrid      |
| Jaruwan | Barcelona | Bangkok | Madrid  | Barcelona, Bangkok, Madrid      |
| Elena   | Barcelona |         | Madrid  | Barcelona, Madrid               |

Now, create a new table:

DAX
Filter = 
DISTINCT(
    UNION(
        SELECTCOLUMNS('Cities', "Combined", 'Cities'[Concat], "Selector", 'Cities'[Bangkok]),
        SELECTCOLUMNS('Cities', "Combined", 'Cities'[Concat], "Selector", 'Cities'[Barcelona]),
        SELECTCOLUMNS('Cities', "Combined", 'Cities'[Concat], "Selector", 'Cities'[Madrid])
    )
)

This table has the combination of all the fields:

ascii
| Combined                     | Selector  |
|------------------------------|-----------|
| Barcelona                    |           |
| Barcelona, Bangkok, Madrid   | Bangkok   |
| Barcelona, Madrid            |           |
| Barcelona                    | Barcelona |
| Barcelona, Bangkok, Madrid   | Barcelona |
| Barcelona, Madrid            | Barcelona |
| Barcelona, Bangkok, Madrid   | Madrid    |
| Barcelona, Madrid            | Madrid    |

Now you can relate the 'Combined' field with the 'Concat' field in our model.

ascii
+-------------------------+          +-------------------------+
|      Cities             |          |      Filter             |
|-------------------------|          |-------------------------|
| Bangkok                 |       +->| Combined	               |
| Barcelona               |       |  | Selector                |
| Concat                  |-------+  |                         |
| Madrid                  |          |                         |
| Names                   |          |                         |
+-------------------------+          +-------------------------+

Notice how the 'Selector' field has all the possible values for the slicer.