Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Sum and partial match

I want to sum partial match. Below formula works fin but I want to add more criteria. When counting for partial match I use IN {"criteria1", "criteria2"} - this is not working in this case. THX   ...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi , Anonymous 

    "Search" returns the number of the character at which a specific character or text string is first found, reading left to right. Search is case-insensitive and accent sensitive.

     

    Here you can  use "CONTAINSSTRING" function to indicate whether one string contains another string.

     

    measure1 =
CALCULATE (
    SUM ( Salg[Net Invoiced Quantity in Base UOM] ),
    CONTAINSSTRING (
        Salg[Product Name],
        "Combi Port E D-X"
    )
        || CONTAINSSTRING ( Salg[Product Name], "Test" )
)

     

     

    Best Regards,
    Community Support Team _ Eason

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , what do mean by partial match here. Not able to get that. Please explain with an example

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    amitchandak By partial match I mean the text strings might be:
    "hhgyj Combi Port E D-X jjij"

    "UFH Combi Port E D-X ccc"

    So by partial match I mean all strings including "Combi Port E D-X"

     

    Hope it makes sense.

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      5 years ago

      Anonymous , search should have worked in that case

      = CALCULATE(SUM(Salg[Net Invoiced Quantity in Base UOM]), filter(Salg, SEARCH("Combi Port E D-X", Salg[Product Name],,0)>0))

       

      If there is more than one such string/search, You need to give with || (or) or && (and) search more then once

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

         

        amitchandak thanks for replying. Not sure I got the last part of your message. This is currently what I have. But it does not work.

        = CALCULATE(SUM(Salg[Net Invoiced Quantity in Base UOM]), 
                SEARCH("Combi Port E D-X", Salg[Product Name],, 0)>0,|| OR(
                SEARCH("TEST", Salg[Product Name],, 0)>0,))