Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Search text using multiple parameters

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?

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    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.

3 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    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 "&&".

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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