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.  But now, i am trying to figure out how to enable SELECT ALL.  The measures below works perfectly when it is SINGLE select only.  Please help.

 

selected keywords = SELECTEDVALUE(table2[key words])

select rows =
IF (
    ISERROR ( FIND ( [selected keywords], MAX ( account[notes] ), 1, 0 ) ),
    0,
    FIND ( [selected keywords], MAX ( account[notes] ), 1, 0 )
)

 

Thank you

Glen

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.

12 Replies

  • AnkitBI's avatar
    AnkitBI
    Solution Sage
    6 years ago

    It's better if we know how you are planning to use this. In meantime, please try below.

    FindResult = 
var SearchKey = filter(Table2,Table2[key Words] = max(Account[notes]))
var CountRows1 = CountRows(SearchKey)

return
if(CountRows1 > 0,1,0)
    • gco's avatar
      gco
      Resolver II
      6 years ago

      Hi AnkitBI ,

      So i have a visual that displays the loan numbers and notes.  And i have a slicer with all the keywords, and it just filters the visual based on notes that matched the keywords/phrases.

       

      Do i create that new measure or replace any of the 2 i already have?

      Thank you 

      Glen

      • AnkitBI's avatar
        AnkitBI
        Solution Sage
        6 years ago

        Hi Glen,

         

        You can use this Measure 'FindResults' and put in a Visual Filter pane for Base visual with Loan and Notes. Set the Visual Filter to value is '1'

        Based on KeyWords selection in Slicer, base visual will only show rows which have matching KeyWords in Notes.

         

        You can create a seperate measure and test it out in sepearte Visual. if it works then you can remove others.

         

        Thanks,

        Ankit Jain