Convert text to number

[macfuller]

I'm thinking this should be simple to identify if a character is a number, but I had to put in an odd workaround.

I have one calculated column that pulls in a project number to the Match Exception table from the Voucher table.

[PO Project]=
CALCULATE (
    FIRSTNONBLANK (
        Vouchers[Project],
        1
    ),
    FILTER (
        Vouchers,
        Vouchers[PO Number] = 'Match Exception'[PO No.]
    )
)

Not all Vouchers have project numbers, and different lines on a voucher may have different projects. Because the Match Exception file does not have the same granularity (e.g. line level data) as the Voucher table, I use the FIRSTNONBLANK construction. It's close enough as we won't mix capital and grant POs.

So I want another column to tell if the project is capital or a grant so I can put it into a slicer for users to select. We use the project field for multiple purposes (bad design, pre-dates me) so we can have capital project IDs or Federal grant IDs. Capital projects start with a number [17COMWB0123], grants with a letter [A43300]. Easy enough I would have thought to use
IF ( ISNUMBER(LEFT('Match Exception'[PO Project],1)), ...

But no, that always returns FALSE. So I tried
IF ( ISNUMBER(VALUE(LEFT('Match Exception'[PO Project],1))), ...

But no, that returns an error because VALUE returns a variant. So this works, but it seems horrible.

=
IF (
    'Match Exception'[PO Project] = "",
    BLANK (),
    IF (
        LEFT('Match Exception'[PO Project],1) IN 
           {"0","1","2","3","4","5","6","7","8","9"},
           "Capital",
           "Grant"
             )
)

I must be missing something obvious?

 

[JustynaMK]

Hi,

I agree that the functionality is a little bit confusing, as your original approach would work perfectly fine in Excel. The unfortunate feature of DAX columns is that a given function is being applied to the entire column, so - even if one row returns an error - the whole column is flagged with an error. I think your function is quite nice, you might want to consider using some alternative, like checking the UNICODE number for the first character -

Check = 
    var code = UNICODE(LEFT('Match Exception'[PO Project], 1))
return
    IF(
        code,
        IF(AND(code >= 48, code <= 57), "number", "text")
    )

Alternatively, what you were trying to do would (almost) work fine in DAX measure - just a small tweak to the formula: