cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
badger123 Member
Member

Search for text in another column and display list

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

ItemPhrase NumberPhrase
ItemA1This phrase is talking about itemA
ItemA2This phrase is talking about itemC and itemD
ItemB3This phrase is talking about itemC and itemE
ItemB4This phrase does not mention the items

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Search for text in another column and display list

@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

3 REPLIES 3
Super User
Super User

Re: Search for text in another column and display list

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 Member
Member

Re: Search for text in another column and display list

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

Highlighted
Super User
Super User

Re: Search for text in another column and display list

@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