Forum Discussion

mattross19's avatar
mattross19
Regular Visitor
4 years ago
Solved

Counting Delimiter Plus 1

I have a calculated column which counts the # of semicolons in a given column, but I want the calculation to add a count of 1 if there is a value greater than 0. 

 

Here is my current formula: 

DIVIDE(len ('Table'[Column]) - Len (SUBSTITUTE(('Table'[Column]), ";","") ),11)
 
Right now it's counting an example of 2 and I want to see "3" (aka adding 1). I plan to divide the delimiters count by a manual # (11 in the example above). 
 
How do I add +1 to the formula if it contains a semicolon?
Need Help
  • mattross19's avatar
    mattross19
    3 years ago

    Thanks, Clay. I wasn't able to get that to work, but I did this get to: 

     

    Measure =
    VAR Str = SELECTEDVALUE('Table'[Column])
    Return
        DIVIDE(len (str) - Len (SUBSTITUTE(Str, ";","") )+1,4)

3 Replies

  • claymcooper's avatar
    claymcooper
    Resolver II
    4 years ago

    Can you wrap it in an IF statement?
    IF(DIVIDE(len ('Table'[Column]) - Len (SUBSTITUTE(('Table'[Column]), ";","") ),11)>0,DIVIDE(len ('Table'[Column]) - Len (SUBSTITUTE(('Table'[Column]), ";","") ),11)+1,DIVIDE(len ('Table'[Column]) - Len (SUBSTITUTE(('Table'[Column]), ";","") ),11))

    • mattross19's avatar
      mattross19
      Regular Visitor
      3 years ago

      Thanks, Clay. I wasn't able to get that to work, but I did this get to: 

       

      Measure =
      VAR Str = SELECTEDVALUE('Table'[Column])
      Return
          DIVIDE(len (str) - Len (SUBSTITUTE(Str, ";","") )+1,4)
  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    4 years ago

    Hi mattross19 

    Thanks for reaching out to us.

    Could you share some sample data and its expected output? thanks

     

     

    Best Regards,

    Community Support Team _Tang

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