Filter by Search Strings

[gco]

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

[AnkitBI]

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]

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]

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

[gco]

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]

@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]

Pls share sample data of both the tables.

[gco]

Hi @AnkitBI ,

Here is a sample pbix file.

https://kcuboard-my.sharepoint.com/:u:/g/personal/gco_kitsapcu_org/EZjXResv22ZOoDbBi5ibLe4BjVwrg33aZ...

Thank you

Glen

[AnkitBI · Accepted solution]

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]

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]

Hi @AnkitBI ,

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

Thanks

Glen

[gco]

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]

Hi @AnkitBI 

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

Thank you

Glen