cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

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 III
Super User III

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

View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

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  

Highlighted
Resolver I
Resolver I

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 III
Super User III

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

View solution in original post

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

August Community Highlights

Check out a full recap of the month!

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors