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

 

Net Invoiced Quantity in Base UOMProduct Name
5huuuh Combi Port E D-X KL26
10MLC
15MLC762

 

 

 

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

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

6 Replies

  • amitchandak's avatar
    amitchandak
    Super 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
        Super 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

    I have add a simple table to show what I mean by partial match amitchandak 

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    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