Forum Discussion

fashpixie's avatar
fashpixie
New Member
8 years ago
Solved

LEN Function Not Working with Subtraction

Email = RIGHT(RE_Usage_Data[User],LEN(RE_Usage_Data[User])-3)&"@xyz.com"

Hello!

 

When I entered the function above into the Data view in Power BI, I receive an error stating "An Argument of function 'RIGHT' has the wrong data type or has an invalid view."

 

In piecing the formula apart, it looks like the "-3" is causing the issue.  With this formula, I'm trying to do the following:

 

  • Take a column of text formatted like this: ar/john.b.smith

          and return the following:

 

 

The RE_Usage_Data[User] field is formatted as text - I'm thinking Power BI is not liking that I'm subtracting 3 from the ar/john.b.smith text.  However, based on how this formula is structured, it should be taking the LEN value of 15 and subtracting 3.  12 should be in the RIGHT formula as the number of characters, and no errors should be returned.  I'm also able to replicate this formula in Excel with no issues - it's also worked in other Power BI files.  

 

Is there anything I can adjust in my formula or any other workarounds to get rid of this error?

 

Thanks for the help! 

 

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    The issue is you have a value with 3 or less characters somewhere in your list.  Try this formula instead.

     

    Email = Var Length = LEN(RE_Usage_Data[User])
RETURN
IF(
    Length <= 3,
    "",
    right([Name], Length - 3) & "@xyz.com"
)

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    The issue is you have a value with 3 or less characters somewhere in your list.  Try this formula instead.

     

    Email = Var Length = LEN(RE_Usage_Data[User])
RETURN
IF(
    Length <= 3,
    "",
    right([Name], Length - 3) & "@xyz.com"
)
    • fashpixie's avatar
      fashpixie
      New Member
      8 years ago

      Thank you for the help! That worked! :smileyhappy:

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    As a measure:

     

    Measure 7 = RIGHT(MAX(Email[Column1]),LEN(MAX(Email[Column1]))-3)&"@xyz.com"
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    I don't know, that worked for me as a column, are you trying to do this aas a measure?