Filtering table by other table,

[Irek]

I have such table, need have functionality that allows me to filter it by slicer in such way that when I choose name for example Bob , my visualistion will show all rows where Bob appears in Key column. 

Data Table (visualisation table looks exactly the same)

Name	Key
Bob	Bob||Jimm||Ben
Jimm	Ben||Jimm||Bob||Adam
Ben	Ben||Jimm||Ben||Adam
Adam	Jimm||Bob||Adam

I achieved it by using dax 

WordCount=

VAR SelectedWord = SELECTEDVALUE('Table'[Name])

RETURN

CALCULATE(

    COUNTROWS(Table),

    CONTAINSSTRING(Table[Key], SelectedWord)

)

I would prefer to do it by using calculated column but so far I couldnt make it. Any suggestions?

Second part of the task im struggling with is to make possible filter one table by another one in same way, 

Have two same looking visualisation tables. first filteres by slicer

When i select Bob in slicer im getting such table

Name	Key
Bob	Bob||Jimm||Ben
Jimm	Ben||Jimm||Bob||Adam
Adam	Jimm||Bob||Adam

Second filtered by first table

effect that I would like to get is when I click on Adam name is to get on second table visual such view

Name	Key
Jimm	Ben||Jimm||Bob||Adam
Adam	Jimm||Bob||Adam

So when im choosing name I want to get rows where selected name appears in Key column. 

[Anonymous · Accepted solution]

Hi, @Irek 

First, create a new table:

Table 2 = SELECTCOLUMNS('Table',"Name2",'Table'[Name])

Then create a new measure and try the following DAX:

Measure = 
VAR _Slicer =
    VALUES ( 'Table 2'[Name2])
VAR _vtable =
    ADDCOLUMNS (
        GENERATE ( 'Table', _Slicer ),
        "AAA", IF ( FIND ( [Name2], [Key],, BLANK () ) <> BLANK (), [Name2] )
    )
VAR _vtable2 =
    SUMMARIZE (
        _vtable,
        [Key],
        "BBB",
            IF (
                LEN ( CONCATENATEX ( _Slicer, [Name2] ) )
                    = LEN (
                        CONCATENATEX ( FILTER ( _vtable, [Key] = EARLIER ( [Key] ) ), [AAA] )
                    ),
                CONCATENATEX ( FILTER ( _vtable, [Key] = EARLIER ( [Key])), [AAA] )
            )
    )
RETURN

    IF (
        CONCATENATEX ( FILTER ( _vtable2, [Key] = 'Table'[Key]), [BBB] )
            <> BLANK (),
        CONCATENATEX ( FILTER ( _vtable2, [Key] = 'Table'[Key] ), [BBB] )
    )

When you select Bob, here is preview:

Then select Adam:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

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

[Irek]

Thank you @Anonymous , 

It works perfectly !