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.

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 ,

 

Thanks again for providing the solution to my problem.  If i may ask another question, how do you create a measure to count the number of rows selected?  I have been playing around with the measures, but could not figure out an accurate way of doing it.

Thank you

Glen

  • gco's avatar
    gco
    Resolver II
    6 years ago

    Hi AnkitBI ,

    You can disregard my previous question about counts.  I figured it out.  RowsCount = COUNTROWS(FILTER(LoanInfo, [FindSearchString] > 0))

     

    Thanks

    Glen