Multiple value slicer

[giogiogio]

Hello everybody: maybe a stupid question from my side, but I am struggling to understand how to solve the following problem which I try to simplify as much as possible in the following example.

I have a table where ID is a value that identifies a unique "project" (and is used to link different tables as this value is unique) and Category is the category within this project falls into.

ID	Category
1	A
2	B
3	A & B
4	A

I'd like now to create a slicer that refers to a single category only:
- if I select B I want to see filtered all projects within category B, thus 2 and 3
- if I select A I want to see filtered all projects within category A, thus 1, 3 and 4.

I was thinking about splitting the entries (e.g. two ID 3 one with A as Category and one with B) but then the one-to-one relationship between tables would be lost.

Is there any workaround to solve this?

Many thanks in advace!

[giogiogio]

Thank you lionel, yours is the best for my needs.
Thank you also to others that replied to my question.

[v-lionel-msft · Accepted solution]

Hi @giogiogio ,

Do like this:

1. Creating a "Category" table by using "Enter data" feature:

2. Creating a measure named "Category_" and adding the measure to the table visual:

Category_ = 
VAR x = 
IF(
    CONTAINSSTRING(
        SELECTEDVALUE(Sheet1[Category]),
        SELECTEDVALUE(Category[Category])
    ),
    TRUE(), FALSE()
)
RETURN
IF( 
    x = TRUE(),
    MAX(Sheet1[Category])
)

 

Since there is no created relationship between the two tables, if you do not add the created measure to the chart, you cannot complete the filtering.

Hope to meet your requirements.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

One to many relationships are not the end of the world.

[amitchandak]

@giogiogio 

Create a new category table with only value A and B

Do not join. try something like this

measure
var _max = maxx(Category,Category[Category])
return
if(isfiltered(Category[Category]),calculate(count(Table[ID])),calculate(count(Table[ID]),search(_max,Table[Category],,0)>0))

Add this measure to visual