How to add / Subtract Character for SUBSTRING - causing some data type issue wrong data type or has

I want to Implement SUBSTRING in Power BI, From a blog where i got to know by using LEFT and RIGHT Text functions we can achive it, which i'm able to write DAX properly, After that i need to subtract last character from that generated output I'm using - 1 to subtract from the generated text output but i'm getting some datatype issue

Request Notes =

Var Len_First = SEARCH("First",'Require Comment'[Note Comment],,0)
var TotalLenght = LEN(''Require Comment'[Note Comment])

Var Substring1 = LEFT(
RIGHT(''Require Comment'[Note Comment],
TotalLenght ),
Len_First )

RETURN Substring1

Im able to get output for the above DAX successfully but i have one issue in it as below, due to data sensivtivity I'm hiding but I'm able to see the Issue here, I'm getting "F"

So to Trim the "F" i tried to alter the code as below but it is throwing this error

Request Notes =

Var Len_First = SEARCH("First",'Require Comment'[Note Comment],,0)
var TotalLenght = LEN(''Require Comment'[Note Comment])

Var Substring1 = LEFT(
RIGHT(''Require Comment'[Note Comment],
TotalLenght ),
Len_First - 1 ) --- I did here as -1 which it is throwing the error "An Argument of function 'LEFT' has the wrong data type or has --an invalid value"
RETURN Substring1

Can some one help me to alter the above DAX code where i need to trim "F" from the above output

Please need support.

Thanks,

Need Help

Show and Tell

v-kkf-msft
4 years ago
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.

16 Replies

Samarth_18
Community Champion
4 years ago
Hi Anonymous ,

Can you try this:-
Request Notes = Var Len_First = SEARCH("First",'Require Comment'[Note Comment],,0) var TotalLenght = LEN(''Require Comment'[Note Comment]) Var Substring1 = LEFT( RIGHT(''Require Comment'[Note Comment], TotalLenght ), int(Len_First) - 1 )

Thanks,
Samarth
- Anonymous
  Not applicable
  4 years ago
  Hi  Samarth_18
  
  Thanks much for your quick response, I have tried it but still it is throwing the same error as below.
  
  My observation is as below,
  var TotalLenght = LEN(''Require Comment'[Note Comment]) -- NO issue with this Variable
  
  But my assumption , the issue cause one is the below one
  
  Var Len_First = SEARCH("First",'Require Comment'[Note Comment],,0)
  The output i get is 99 , but i want to consider till 98
  
  the same above when i try to add -1 its not doing it
  Var Len_First = SEARCH("First",'Require Comment'[Note Comment],,0) -1
  its throwing wrong value
  
  So alternately i tried to do as below
  Var Len_First = LEN(SEARCH("First",'Require Comment'[Note Comment],,0)) -1
  Its also giving wrong out and trowing the same error  "An Argument of function 'LEFT' has the wrong data type or has --an invalid value"
  
  Need support on this how to fix, bec i need to do the similar measures for 4 , if i fixed one i can solve remaining where i need to add + 10 and + 15 for that
  
  Thanks
  - Samarth_18
    Community Champion
    4 years ago
    Anonymous Please try this:-
    m = VAR Len_First = SEARCH ( "First", 'Require Comment'[Note Comment],, 0 ) VAR TotalLenght = LEN ( 'Require Comment'[Note Comment] ) VAR Substring1 = IF ( Len_First <> blank, LEFT ( RIGHT ( 'Require Comment'[Note Comment], TotalLenght ), INT ( Len_First ) - 1 ) ) RETURN Substring1
Anonymous
Not applicable
4 years ago
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
- Samarth_18
  Community Champion
  4 years ago
  Anonymous 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?
Anonymous
Not applicable
4 years ago
Hi Samarth_18 ,

Ok Sure,, i will try to mock the data and will share the data with PBIX file with calc colum craeted , but how to upload a PBIX file here I'm unable to do that and how to share, Please help so that i can share it here

Thanks,
- Samarth_18
  Community Champion
  4 years ago
  Anonymous Upload it in drive and share.
Anonymous
Not applicable
4 years ago
Hi Samarth_18

I can do that share from gmail drive right?, so that i can share it.
- Samarth_18
  Community Champion
  4 years ago
  Yes you can share.
Anonymous
Not applicable
4 years ago
Hi Samarth_18

Please find the gdrive link, which i'm able to upload , Please do help on my concern which i'm facing issue.
Thanks
- Samarth_18
  Community Champion
  4 years ago
  Anonymous I think you missed to add URL?
  - Anonymous
    Not applicable
    4 years ago
    Hi Samarth_18
    
    Ya sorry i have missed it to add.
    
    Please do support on my concern which i'm facing issue.
    
    Deleted link for no support update.
    
    Thanks,
v-kkf-msft
Community Support
4 years ago
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.
- Anonymous
  Not applicable
  4 years ago
  Hi v-kkf-msft
  
  Thanks Winniz,
  
  Working as expected, Thanks for the suppot.
  
  I know this is not correct, but i dont have anyohter option, i have posted request related to write sql subquery to DAX , can you help on the below if possible only.
  
  https://community.powerbi.com/t5/Desktop/Power-BI-DAX-subquery-for-multiple-Tables-Complex-DAX-need-help/m-p/2362509#M850391
  
  Thanks

Forum Discussion

How to add / Subtract Character for SUBSTRING - causing some data type issue wrong data type or has

16 Replies

Related content

Subtraction calulation

Substring in Power BI ?!

Find multiple substring DAX CONTAINSSTRING

Can't subtract

Export data strange characters