Forum Discussion

gco's avatar
gco
Resolver II
6 years ago
Solved

Filter by Search Strings

Hi there,   I have a list of keywords and would like to filter a column account[notes] based on those keywords.  Someone helped me by giving me the calculated measure below.  And it works perfectly...
Need Help
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    Hi - Apologies, should have asked for Sample Data earlier. For your case, we will need to user Loop. Please use below Measure

     

    FindSearchString = 
// Get list of Selected SearchStrings Values
var SearchValues = Values(SearchStrings[SearchStringVal])
// Run a Loop to check if selected SearchStrings values are present in Comments.
var FindVal = ADDCOLUMNS(SearchValues,"Present",Find([SearchStringVal],max(LoanInfo[Comments]),1,0))
//Get Sum, it will be '0' if Comments doesn't contains any of the Selected Values else > 0
var SumTotal = sumx(FindVal,[Present])

Return SumTotal

    Update PBIX file below.

    http://www.mediafire.com/file/bhv7gwuyylcbq75/For_Upload_Ankit.pbix/file

     

    Thanks
    Ankit Jain

    Do Mark it as solution if the response resolved your problem. Do Kudos the response if it seems good and helpful.

gco's avatar
gco
Resolver II
6 years ago

AnkitBI 

 

The table i was working on are SearchStrings with all the keywords and LoanInfo that has the LoanNumber and Comments that i wanted to filter.

Thank you

Glen

AnkitBI's avatar
AnkitBI
Solution Sage
6 years ago

Pls share sample data of both the tables.

  • gco's avatar
    gco
    Resolver II
    6 years ago

    Hi AnkitBI 

     

    I am going to upload a stripped down pbix file shortly.

    Thank you

     

    Glen

    • AnkitBI's avatar
      AnkitBI
      Solution Sage
      6 years ago

      Hi - Apologies, should have asked for Sample Data earlier. For your case, we will need to user Loop. Please use below Measure

       

      FindSearchString = 
// Get list of Selected SearchStrings Values
var SearchValues = Values(SearchStrings[SearchStringVal])
// Run a Loop to check if selected SearchStrings values are present in Comments.
var FindVal = ADDCOLUMNS(SearchValues,"Present",Find([SearchStringVal],max(LoanInfo[Comments]),1,0))
//Get Sum, it will be '0' if Comments doesn't contains any of the Selected Values else > 0
var SumTotal = sumx(FindVal,[Present])

Return SumTotal

      Update PBIX file below.

      http://www.mediafire.com/file/bhv7gwuyylcbq75/For_Upload_Ankit.pbix/file

       

      Thanks
      Ankit Jain

      Do Mark it as solution if the response resolved your problem. Do Kudos the response if it seems good and helpful.

      • gco's avatar
        gco
        Resolver II
        6 years ago

        Hi AnkitBI ,

        You are a genius!!! where do you guys learn all these stuff?  I go on powerbi classes but they never touch on these topics or come close to any advanced DAX queries.

         

        Your measure works perfectly for what i was trying to do.  Thank you again for taking the time to share the answer!!!

        Glen