Search for text in another column and display list

[badger123]

I have searched the forum for this question and have seen lots about searching in a column but haven't found an answer to my use case.

Here is what I am trying to achieve (tables simplified). I have two tables which are joined by item. My page has a slicer to select a single Item to focus on. On this page, there is a table showing the phrases related to the item. E.g. if ItemA is selected, the table shows phrases 1 and 2. 

On this page, I also want to show a list of the Items that are mentioned in the selected Phrases. E.g. in the example above, the list would show Items A, C and D. 

Table 1

Item

ItemA

ItemB

ItemC

ItemD

ItemE

Table 2

Item	Phrase Number	Phrase
ItemA	1	This phrase is talking about itemA
ItemA	2	This phrase is talking about itemC and itemD
ItemB	3	This phrase is talking about itemC and itemE
ItemB	4	This phrase does not mention the items

Any help would be much appreciated! I can't figure this one out!! 

[AlB]

Hi @badger123

The code below assumes that your Table1[Item]  in a slicer and  there's a 1-to-many relationship through 'Item' between Table1 and Table2, as you say. Create this measure and place it in a card visual:

ItemsInSelectedPhrases = 
CONCATENATEX (
    Table2;
    CONCATENATEX (
        FILTER ( ALL ( Table1 ); SEARCH ( Table1[Item]; Table2[Phrase]; 1; 0 ) > 0 );
        Table1[Item];
        ", "
    );
    UNICHAR ( 10 )
)

It shows the items found in each phrase in separate lines.

Here's a file with the suggested solution based on your sample data  

[badger123]

Thanks @AlB - is there a way I can display the results in a table? 

[AlB · Accepted solution]

@badger123

You mean in a table visual? Here's a file with the steps detailed below.

1. Create an additional calculated table:

ShowTable = Table1

2.  Place ShowTable[Item] in a table visual

3. Create this measure:

ShowMeasure =
VAR _Result =
    CONCATENATEX (
        Table2;
        CONCATENATEX (
            FILTER ( ALL ( Table1 ); SEARCH ( Table1[Item]; Table2[Phrase]; 1; 0 ) > 0 );
            Table1[Item];
            ". "
        );
        UNICHAR ( 10 )
    )
VAR _UniqueItems =
    FILTER ( ALL ( Table1 ); SEARCH ( Table1[Item]; _Result; 1; 0 ) > 0 )
RETURN
    IF ( SELECTEDVALUE ( ShowTable[Item] ) IN _UniqueItems; 1 )

4. Place [ShowMeasure] in the visual level filters of the table visual and select 'Show items when the value is' --> 1