slicer to filter all name occurrences

[Eyal]

Hi all

I could find a solution althou it looks like somting simple...

I have a personal table (table 1 )with names of my emploies

Name  ID 
Ana  1

Bela  2

Elsa  3

Secound table (table 2) with task assinments

Task   responsible

1       Ana

2       Bela

3       Elsa; Ana

4       Ana

I use a slicer with based ontable 1 to filter table 2 by the name.

How do I filter such that if I select Ana i will get all the rows where Ana apperes ?

Task   responsible

1       Ana

3       Elsa; Ana

4       Ana

many thanks for your help

Eyal

[v-easonf-msft · Accepted solution]

Hi, @Eyal 

Try measure formula as below:

filter = 
VAR flag =
    IF (
        CONTAINSSTRING ( MAX ( Table2[responsible] ), MAX ( Table1[Name] ) ),
        1,
        0
    )
RETURN
    IF ( ISFILTERED ( Table1[Name] ), FLAG, 0 )

Then apply this new field to visual filter pane.

Best Regards,
Community Support Team _ Eason

[Eyal · Accepted solution]

Hi Eason

Your solution works great!! many thanks

I add a slight modification

filter = 
VAR flag =
    IF (
        CONTAINSSTRING ( MAX ( Table2[responsible] ), MAX ( Table1[Name] ) ),
        1,
        0
    )

Var num =DISTINCTCOUNT('Table1'[Name]) // count to see how many names were selected

return

if(num>1,1,flag) // this way if multiple name are selected the table is not filtered .

[v-easonf-msft · Accepted solution]

Hi, @Eyal 

Try measure formula as below:

filter = 
VAR flag =
    IF (
        CONTAINSSTRING ( MAX ( Table2[responsible] ), MAX ( Table1[Name] ) ),
        1,
        0
    )
RETURN
    IF ( ISFILTERED ( Table1[Name] ), FLAG, 0 )

Then apply this new field to visual filter pane.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@Eyal , One option is that you split responsible in power query using split by delimiter into rows. Then you can join both table on name and check

Or if select only one value and keep same format

countrows(filter( Table1, containsstring(Table2[responsible], selectedvalue(Table1[Name]) ) ))