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

Pls share sample data of both the tables.

  • 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

  • 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