Search text using multiple parameters

[Anonymous]

Hello - 

I wonder if anyone can spot the problem with my dax query? 

I am trying to search within a text using multiple parameters - The parameters are "Text search 1" ,"Text Search 2" and "Text Search 3" and the text i am trying to search is "Casenote text" 

The formula is supposed to provide a disrinct count of record ID if any one of the 3 searches is found within the text. 

I tried a few different formulas -  here is my latest attempt using search and ||. 

Casenote mention count = CALCULATE(DISTINCTCOUNT('All Casenotes'[CN Record ID]),filter('All Casenotes',search(max('Text Search'[Text Search]) || max('Text Search 2'[Text Search 2]) || max('Text Search 3'[Text Search 3]),'All Casenotes'[Casenote text],,0)))

the error it is throwing up is "Cannot convert "text" value of type text to True/False?

I also want to do something simliiar with And as the operator...

Would be grateful if anyone could suggest where i am going wrong?

[Anonymous]

Thansk very much both, seems like I need to learn more about the use of variables.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

You may try the following measure to see if it helps.

Casenote mention count =
VAR s1 =
    MAX ( 'Text Search'[Text Search] )
VAR s2 =
    MAX ( 'Text Search 2'[Text Search 2] )
VAR s3 =
    MAX ( 'Text Search 3'[Text Search 3] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'All Casenotes'[CN Record ID] ),
        FILTER (
            'All Casenotes',
            CONTAINSSTRINGEXACT ( [Casenote text], s1 )
                || CONTAINSSTRINGEXACT ( [Casenote text], s2 )
                || CONTAINSSTRINGEXACT ( [Casenote text], s3 )
        )
    )

Best Regards

Allan

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

[DataInsights]

@Anonymous,

Try this measure:

Casenote mention count = 
VAR vCasenote =
    MAX ( 'All Casenotes'[Casenote text] )
VAR vText1 =
    SUMX (
        'Text Search',
        SEARCH ( vCasenote, 'Text Search'[Text Search], 1, 0 )
    )
VAR vText2 =
    SUMX (
        'Text Search 2',
        SEARCH ( vCasenote, 'Text Search 2'[Text Search 2], 1, 0 )
    )
VAR vText3 =
    SUMX (
        'Text Search 3',
        SEARCH ( vCasenote, 'Text Search 3'[Text Search 3], 1, 0 )
    )
VAR vResult =
    IF (
        vText1 > 0
            || vText2 > 0
            || vText3 > 0,
        DISTINCTCOUNT ( 'All Casenotes'[CN Record ID] )
    )
RETURN
    vResult

For the AND version, replace "||" with "&&".