Forum Discussion
How to add / Subtract Character for SUBSTRING - causing some data type issue wrong data type or has
Hi Anonymous ,
Because there are 0 values in the return value of your variable "Var Len_First = SEARCH("First",'Require Comment'[Note Comment],,0)", and the -1 will exist after subtracting 1. And using -1 as an argument in the function LEFT will result in an error.
Please try modifying the variable to the following formula.
Var In_Substring = LEFT ( RIGHT ( 'Require Comment'[Note Comment], TotalLenght ), IF ( Len_FirstCallback = 0, 0, Len_FirstCallback - 1 ) )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Samarth_18
I have tried that but still throwing the error,
m =
VAR Len_First =
SEARCH ( "First", 'Require Comment'[Note Comment],1, 0 ) -- Adding 1 or 2 or 3 or 4 i have adding till 7 ... does'nt change any difference in the output it is still showing 90
VAR TotalLenght =
LEN ( 'Require Comment'[Note Comment] )
VAR Substring1 =
IF (
Len_First <> blank,
LEFT (
RIGHT ( 'Require Comment'[Note Comment], TotalLenght ),
INT ( Len_First ) - 1 -- the complete problem and culprit is "-" when i add "- 1" ( our assumption like 90 - 1 = 89 , which "F" will be removed at end ) this is throwing error of datatype and Invalid value becuase of "-" for the same when i Put " + 1 " ( INT ( Len_First ) + 1 ) this is not causing any issue giving 91 and output ends with Fi
)
)
RETURN
Substring1
Even i tried to create a seperate calc column as below and tried to use into other DAX still it is causing the issue.
Len_First = SEARCH("First",'Require Comment'[Note Comment],,0) -1 this is giving 89 for a value
when i use this "Len_First" in the formula as below
Request Notes =
var TotalLenght = LEN(''Require Comment'[Note Comment])
Var Substring1 = LEFT(
RIGHT(''Require Comment'[Note Comment],
TotalLenght ),
'Require Comment'[Len_First] )
RETURN Substring1
Still it is throwing the same error, unable to solve it
Community ChampionAnonymous Something really missing here since I have tried to generate same scenario and its works fine while creating a column. Is it possible for you to share your PBIX after removing sensitive data or create a sample file with similar data?