Dynamic Text Column based on another column with multiple return options

[Anonymous]

Hi - I am having a problem with a column I've created. I have a column in my data that I'd like to use as a filter; however, there are multiple values in this column I'd like to parse out. See table below for how data is being captured:

Product	Region
A	South
B	East; South
C	East; North; South
D	North; West

I want to create a filter for "Region" but have it only give "North", "South", "East", and "West" as the drop down options. Then, in the Power BI report, I still would like the Region data to show up as is in the table (i.e. details of multiple regions tagged to a product should not be lost, but for ease of filtering I don't want to select 3 different options just to see all products tagged to South). 

I'm using the following to create a new column but it's not giving me all the rows tagged to a specific region properly. For example, the following would only return "South" if I clicked on "South" instead of "East; South", etc. as the "East; South" row only shows up if I click on East (it comes first in the code below, but I want to create a code that solves this issue):

Region =

SWITCH(

    TRUE(),

CONTAINSSTRING('Sheet1'[Region],"North")=TRUE(),"North",

CONTAINSSTRING('Sheet1'[Region],"East")=TRUE(),"East",

CONTAINSSTRING('Sheet1'[Region],"West")=TRUE(),"West",

CONTAINSSTRING('Sheet1'[Region],"South")=TRUE(),"South")

How can I write a measure or column to pick up all the rows tagged to a specific region? I.e. for the "East; South" example, how can I ensure that when I create a filter with only the 4 regions, when I click on "East" and "South", "East; South" shows up both times? 

Thanks in advance!

[TomMartens · Accepted solution]

Hey @Anonymous ,

my recommendation is using data modeling to tackle your challenge. I derived two additional tables from the column Region of the table you provided, see the screenshot:

The table unique region string contains unique values of the region column:

The table slicer starts with the unique values of the region column, this column is duplicated. The duplicated column is renamed to slicer. The transform "Split by delimiter" is used as follows:

The result:

Based on these tables you can build the data model from the first picture, pay attention to the cross filter direction.

A report will show you this:

Hopefully, this helps to tackle your challenge.

Regards,

Tom