Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

MAX(textColumn) how to return the max numeric

I have a table with text column that stores text and numbers.  I have a view that filters the column to ignore the text and show the rows with numbers only.  In that view I want to use the MAX function to return the largest values but since the column is text it yield unwanted results, for example intead of returning 10 it may return 3.  

 

Is it possible to use an agregate function in a text column to yield returns as if it was a numeric column?  I attempted embedding the VALUE and FORMAT function inside the MAX function but that didnt work.

Need Help
  • danextian's avatar
    danextian
    8 years ago

    Try this modified formula which should attempt to extract the number value from a text column and returns blank() if the execution results to an error:

     

    Numeric Value =
IF (
    NOT ( ISERROR ( VALUE ( Table[AlphanumericColumn] ) ) ),
    VALUE ( Table[AlphanumericColumn] )
)

6 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    8 years ago

    Hi Anonymous,

     

    I don't think MAX calculation for non-numeric value is possible. I would suggest to create a calculated column to extract the numeric values in your text column. Example:

     

    Numeric Value =
IF (
    ISNUMBER ( VALUE ( Table[AlphanumericColumn] ) ),
    VALUE ( Table[AlphanumericColumn] )
)

     

    And then use this column to calculate for the max value. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi danextian

       

      Thank you for your reply.  I attempeted what you suggested but it complains that it cannot convert value of type Text to type Number.

       

       

       

       

       

       

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        8 years ago

        Try this modified formula which should attempt to extract the number value from a text column and returns blank() if the execution results to an error:

         

        Numeric Value =
IF (
    NOT ( ISERROR ( VALUE ( Table[AlphanumericColumn] ) ) ),
    VALUE ( Table[AlphanumericColumn] )
)