Attempting to count affected rows, not total occurrences of a keyword!

[dubya]

Hello, 

I am attempting to count the number of application's comments that include a key word, not the total count of the keyword. 

i.e. creating the measure on the right from the table on the left. 

I have put together this measure, but this counts the total amount of keyword occurences. 

Applications with Keywords Count Measure =

VAR _search_word =

    SELECTEDVALUE ('Keywords Table'[Keywords])

VAR _result =

    CALCULATE (

        COUNTROWS ( 'Application Detail Table' ),

        CONTAINSSTRING ( 'Application Detail Table'[Comments], _search_word )

    )

RETURN

    _result

Please help!

Thank you! 

[lbendlin · Accepted solution]

Does it have to be DAX?

Keywords:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs/PT1GK1YlWcixLLUpMTwWznRKBYrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Keyword = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Application count", (k)=> Table.RowCount(Table.SelectRows(#"Application Detail",each Text.PositionOf([Comments],k[Keyword])>-1)),Int64.Type)
in
    #"Added Custom"

Application Count DAX = var k=[Keyword] return CALCULATE(countrows('Application Detail'),SEARCH(k,'Application Detail'[Comments],,0)>0)

[v-kaiyue-msft · Accepted solution]

Hi @dubya ,

You can try the following dax expression.

ApplicationsWithKeywordCount = 
VAR Keyword = SELECTEDVALUE('Keywords Table'[Keywords])
RETURN
CALCULATE(
    DISTINCTCOUNT('Application Detail Table'[Application #]),
    SEARCH(Keyword, 'Application Detail Table'[Comments], 1, 0) > 0
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[lbendlin · Accepted solution]

Does it have to be DAX?

Keywords:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs/PT1GK1YlWcixLLUpMTwWznRKBYrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Keyword = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Application count", (k)=> Table.RowCount(Table.SelectRows(#"Application Detail",each Text.PositionOf([Comments],k[Keyword])>-1)),Int64.Type)
in
    #"Added Custom"

Application Count DAX = var k=[Keyword] return CALCULATE(countrows('Application Detail'),SEARCH(k,'Application Detail'[Comments],,0)>0)