Column Data Type is Text but ISTEXT measure says otherwise

[Anonymous]

Hi,

I'm connected to a Published Dataset and creating a dashboard with it on the Desktop application. I have a calculated measure referencing the Item Number column that needs to use either FIND or SEARCH to determine location of a Hyphen '-'. It is working half the time and not the other half. I couldn't figure out why it could find the hyphen in something like '007-2832L' and then would not work on something like '11WST-038'. I finally just added a measure ISTEXT(itemnumber) to see what it would say, and sure enough the rows it wasn't working on said FALSE and TRUE on the rows that the formulas were working on. I don't know how to correct this as my system admin has the column data type set correctly to TEXT. I tried using CONVERT to force it into TEXT, that didn't work. Out of curiousity I downloaded the .pbix of the dataset, made a table of just itemnumber and a measure ISTEXT(itemnumber), and nothing came back FALSE. Very confused at this point. I've refreshed my file, closed and re-opened. Nothing seems to correct it.  Would very much appreciate any help. 

[v-yiruan-msft]

Hi @Anonymous ,

I just created a calculated column as below to check whether the item number include the hyphen "-", it can return the correct result just as shown in below screenshot. And it can get the correct values if I use ISTEXT fuction to check whether the value of item number is Text. Could you please provide the related formula of your calculated measure which using FIND or Search function? Thank you.

Is include hyphen = IF ( SEARCH ( "-", 'Table'[ItemNumber], 1, 0 ), "Yes", "No" )

Best Regards

[Anonymous]

I cannot make a calculated column, I am connected to published dataset so column creation is restricted. My measure formulas:

'= ISTEXT(SELECTEDVALUE('Table'[ItemNumber]))'

To find hyphen:

'Var hyphen = "-"

Var itemstr = SELECTEDVALUE('Table'[ItemNumber])

Var firsthyphen = FIND(hyphen, itemstr,1,0)'

I found a common theme though - there is a sum of inventory column, when I put that quantity field into the table, all my FALSE values are tied to those items where the Sum of Inventory was zero, so as a bandaid I can use that to sort out. 

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I can get the correct result when create a measure using the same formula with yours just as shown in below screenshot. The precondition is the field ItemNumber need to be put on the related visual...

Include hypen = 
VAR hyphen = "-"
VAR itemstr =
    SELECTEDVALUE ( 'Table'[ItemNumber] )
VAR firsthyphen =
    IFERROR ( FIND ( hyphen, itemstr, 1, 0 ), 0 )
RETURN
    IF ( firsthyphen > 0, "Yes", "No" )

Best Regards

[Anonymous]