locked
Converting Text string to Numeric Value RRS feed

  • Question

  • I have a column that I'm trying to call into a calculated measure to determine an expected contract amount (Terms in Month). The problem is that some of the terms are defined as text strings (MTM, Coterminous, One-time) while others are numbers (12, 36, etc). The entire column is recognized as text. I have a numeric value that management has agreed would be an acceptable substitution (MTM=1, Coterminous=6) and so on. I can't however, figure out how to convert those texts to a number since they are different data types.

    I've tried a nested IF statement, as well as a LOOKUPVALUE

    Any help would be appreciated. I'm doing this in Power Pivot, so am limited to DAX formulas. (I know VLOOKUP would be great right about now)

    Thursday, June 11, 2015 5:23 PM

Answers

  • You should be able to add in VALUE to your IF statement and it should work.

    =IF(Sheet1[Value] = "MTM", 1, IF(Sheet1[Value] = "Coterminous",6,VALUE(Sheet1[Value])))

    • Marked as answer by Caitlin Knox Thursday, June 11, 2015 6:13 PM
    Thursday, June 11, 2015 6:03 PM

All replies

  • Is your nested IF statement in a separate calculated column or inside your measure?
    Thursday, June 11, 2015 5:39 PM
  • It would be a separate calculated column, and then I would just call that column instead of the original.
    Thursday, June 11, 2015 5:44 PM
  • You should be able to add in VALUE to your IF statement and it should work.

    =IF(Sheet1[Value] = "MTM", 1, IF(Sheet1[Value] = "Coterminous",6,VALUE(Sheet1[Value])))

    • Marked as answer by Caitlin Knox Thursday, June 11, 2015 6:13 PM
    Thursday, June 11, 2015 6:03 PM
  • YAHTZEE. Thanks, appreciate it.
    Thursday, June 11, 2015 6:13 PM