Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Wendy_WL
Regular Visitor

How to list out my output results

Lookup keyword using "Searchsheet" table to find match in "Stringsheet" table.

2 types of conditions:

(i) When BOTH [Search1] AND [Search2] are found in "Stringsheet" table;

(ii) When Either [Search1] OR [Search2] are found in "Stringsheet" table;

List out the results found.  Appreciate your help greatly to list out the results found in yellow highlights below.  I am not familiar with List function.  Thank you.

Link for my Power Bi file : List out results 

 

Screenshot 2024-09-07 142720.png

 

ContainsString AND =
  VAR __String = [String1]
  VAR __Table =
    ADDCOLUMNS(
      'Searchsheet',
      "Found", CONTAINSSTRING( __String, [Search1]) &&
      CONTAINSSTRING( __String, [Search2]))   
  VAR __Result = IF( COUNTROWS( FILTER( __Table, [Found] = TRUE() ) ) > 0, TRUE(), FALSE() )
RETURN
  __Result
 
ContainsString OR =
  VAR __String = [String1]
  VAR __Table =
    ADDCOLUMNS(
      'Searchsheet',
      "Found", CONTAINSSTRING( __String, [Search1]))
  VAR __Table1 =
    ADDCOLUMNS(
      'Searchsheet',
      "Found", CONTAINSSTRING( __String, [Search2]))  
  VAR __Result = IF( COUNTROWS( FILTER( __Table, [Found] = TRUE() ) ) > 0, TRUE(), IF( COUNTROWS( FILTER( __Table1, [Found] = TRUE() ) ) > 0, TRUE(), FALSE() ) )
RETURN
  __Result
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Wendy_WL ,

 

I’ve made a test for your reference:

 

List For And =

CONCATENATEX(

    FILTER(

        Searchsheet,

        CONTAINSSTRING(Stringsheet[String1], Searchsheet[Search1]) &&

        CONTAINSSTRING(Stringsheet[String1], Searchsheet[Search2])

    ),

    Searchsheet[Search1] & " " & Searchsheet[Search2],

    "; "

)

 

 

List For Or =

CONCATENATEX(

    FILTER(

        Searchsheet,

        CONTAINSSTRING(Stringsheet[String1], Searchsheet[Search1]) ||

        CONTAINSSTRING(Stringsheet[String1], Searchsheet[Search2])

    ),

    Searchsheet[Search1] & " " & Searchsheet[Search2],

    "; "

)

 

vbofengmsft_0-1725931086309.png

 

 

Best Regards,
Bof

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @Wendy_WL ,

 

I’ve made a test for your reference:

 

List For And =

CONCATENATEX(

    FILTER(

        Searchsheet,

        CONTAINSSTRING(Stringsheet[String1], Searchsheet[Search1]) &&

        CONTAINSSTRING(Stringsheet[String1], Searchsheet[Search2])

    ),

    Searchsheet[Search1] & " " & Searchsheet[Search2],

    "; "

)

 

 

List For Or =

CONCATENATEX(

    FILTER(

        Searchsheet,

        CONTAINSSTRING(Stringsheet[String1], Searchsheet[Search1]) ||

        CONTAINSSTRING(Stringsheet[String1], Searchsheet[Search2])

    ),

    Searchsheet[Search1] & " " & Searchsheet[Search2],

    "; "

)

 

vbofengmsft_0-1725931086309.png

 

 

Best Regards,
Bof

tharunkumarRTK
Super User
Super User

your file is not accessible. Please grant the permissions

Thank you very much,  I have grant the permission. 

Here is the link again :

https://drive.google.com/drive/folders/1fWS2SbPwFJwf6Xp8aqdYtIJ4KDCU87hb?usp=drive_link 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.