Forum Discussion

Portrek's avatar
Portrek
Icon for Resolver III rankResolver III
6 years ago
Solved

Calculate with filters and Contaimstring with OR

Hello everyone, i need help for elaborate a med.

 

I need  to calculate the amount the records in the collunm by type text that contaim the diferents words.

 

For Example, I need a formula that brings me the added value if the column contains the text XXXX or YYYY or ZZZZZ

 

I thing that has beem something with CALCULATE / FILTER / CONTAINSTRING (or other text function) with the funcition OR

 

How i can do that ?

 

Thanks a lot for the answers.

 

 

  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Portrek 

     

    Based on your description, I assume that you want to count rows which contains specific words and get the text which excludes the specific words. I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create two measures as below.

    CountRecords = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        CONTAINSSTRINGEXACT('Table'[Text],"XXXX")||
        CONTAINSSTRINGEXACT('Table'[Text],"YYYY")||
        CONTAINSSTRINGEXACT('Table'[Text],"ZZZZ")
    )
)

Result = 
var _text = SELECTEDVALUE('Table'[Text])
return
SWITCH(
    TRUE(),
    CONTAINSSTRINGEXACT(_text,"XXXX"),
    SUBSTITUTE(_text,"XXXX",""),
    CONTAINSSTRINGEXACT(_text,"YYYY"),
    SUBSTITUTE(_text,"YYYY",""),
    CONTAINSSTRINGEXACT(_text,"ZZZZ"),
    SUBSTITUTE(_text,"ZZZZ","")
)

     

    Result:

     

    Best Regards

    Allan

     

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

2 Replies

  • nvprasad's avatar
    nvprasad
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Hi,

     

    Can you try the below function?

    Count_string =
    CALCULATE (
    COUNTROWS ( 'Table' ),
    CONTAINSSTRING ( Table[Column], "XXXX" )
    || CONTAINSSTRING ( Table[Column], "YYYY" )
    )

    Appreciate a Kudos! 🙂
    If this helps and resolves the issue, please mark it as a Solution! 🙂

    Regards,
    N V Durga Prasad

     

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Portrek 

     

    Based on your description, I assume that you want to count rows which contains specific words and get the text which excludes the specific words. I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create two measures as below.

    CountRecords = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        CONTAINSSTRINGEXACT('Table'[Text],"XXXX")||
        CONTAINSSTRINGEXACT('Table'[Text],"YYYY")||
        CONTAINSSTRINGEXACT('Table'[Text],"ZZZZ")
    )
)

Result = 
var _text = SELECTEDVALUE('Table'[Text])
return
SWITCH(
    TRUE(),
    CONTAINSSTRINGEXACT(_text,"XXXX"),
    SUBSTITUTE(_text,"XXXX",""),
    CONTAINSSTRINGEXACT(_text,"YYYY"),
    SUBSTITUTE(_text,"YYYY",""),
    CONTAINSSTRINGEXACT(_text,"ZZZZ"),
    SUBSTITUTE(_text,"ZZZZ","")
)

     

    Result:

     

    Best Regards

    Allan

     

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