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

[ramshoney1225]

Hi 

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,

[v-kkf-msft · Accepted solution]

Hi @ramshoney1225 ,

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.

[ramshoney1225]

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-...

Thanks

[ramshoney1225]

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]

@ramshoney1225 I think you missed to add URL?

[ramshoney1225]

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,

[ramshoney1225]

Hi @Samarth_18 

I can do that share from gmail drive right?, so that i can share it.

[Samarth_18]

Yes you can share.

[ramshoney1225]

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]

@ramshoney1225 Upload it in drive and share.

[ramshoney1225]

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]

@ramshoney1225 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?

[Samarth_18]

Hi @ramshoney1225 ,

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

[ramshoney1225]

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]

@ramshoney1225 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

[ramshoney1225]

Hi @Samarth_18 ,

Ya i tried it, but still it is throwing error as below.

Thanks,

[Samarth_18]

@ramshoney1225  Seems now error is different so you can try this now:-

m =
VAR Len_First =
    SEARCH ( "First", 'Require Comment'[Note Comment],1, 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