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

Hi AnkitBI ,

 

For some reason the measure you gave does not show any results.  I modified the SearchKey var and was able to get results.  But it only works on single select.  If i select more than 1 keywords, then it displays everything.

 

Measure =
var SearchKey = filter(LoanInfo, FIND(SELECTEDVALUE(SearchStrings[SearchStringVal]), MAX(LoanInfo[FraudCommentsPreview]),1,0))
var CountRows1 = CountRows(SearchKey)

return
if(CountRows1 > 0, 1, 0)
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.