Forum Discussion

abujouz86's avatar
abujouz86
Icon for Helper II rankHelper II
5 years ago

Remove characters convert text to numbers

I have a column of data that includes for example "1 person" or "10/1". I want to remove the characters leaving only the number and if there is a / I want to add the number to the left to the number to the right (e.g. 10/1 = 11).

 

In excel I would write the formula this way:

=IF(ProdRouteTrans[CATEGORYID]="","",
VALUE(TRIM(IF(ISERROR(FIND("/",ProdRouteTrans[CATEGORYID])),
LEFT(ProdRouteTrans[CATEGORYID],2),
LEFT(ProdRouteTrans[CATEGORYID],
FIND("/",ProdRouteTrans[CATEGORYID])-1))))
+ IF(ProdRouteTrans[CATEGORYID]="","",
VALUE(TRIM(IF(ISERROR(FIND("/",ProdRouteTrans[CATEGORYID])),0,MID(ProdRouteTrans[CATEGORYID],
FIND("/",ProdRouteTrans[CATEGORYID])+1,2))))))
 
But when I write the dax in pbi I get an error message "Expressions that yield variant data-type cannot be used to define calculated columns." Can someone help me fix this formula? Thank you!
Need Help

12 Replies

    • abujouz86's avatar
      abujouz86
      Icon for Helper II rankHelper II
      5 years ago

       

      CATEGORYID
      2 people
      2 people
      8/1 people
      8/1 people
      8/1 people
      8/1 people
      2 people
      5/1 people
      5/1 people
      4/1 people
      3 people
      4 people
      4 people
      4 people
      9/1 people
      5/1 people
      8/1 people
      6/1 people
      8/1 people
      8/1 people
      5/1 people
      7/1 people
      8/1 people
      8/1 people
      15/1 people
  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    5 years ago

    abujouz86  Try this:

    Column =
VAR CurrentCategory = Abu[Category]
VAR AllAlphabets =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 65, 90, 1 ),
            "Alphabets", LOWER ( UNICHAR ( [Value] ) )
        ),
        "Alphabets", [Alphabets]
    )
VAR RemovePeople =
    SUBSTITUTE ( CurrentCategory, " people", "" )
VAR FilterValues =
    FILTER (
        ADDCOLUMNS (
            GENERATESERIES ( 1, LEN ( RemovePeople ), 1 ),
            "v", MID ( RemovePeople, [Value], 1 )
        ),
        [v] <> "/"
            && NOT [v] IN AllAlphabets
    )
VAR Result =
    SUMX ( FilterValues, INT ( [v] ) )
RETURN
    Result
    • abujouz86's avatar
      abujouz86
      Icon for Helper II rankHelper II
      5 years ago

      Thank you - I replaced "Abu[category]". However I get the error message "Cannot convert value '' of type Text to type Number."

      • AntrikshSharma's avatar
        AntrikshSharma
        Icon for Community Champion rankCommunity Champion
        5 years ago

        abujouz86  Please ignore the previous post, it didn't account for 15/1 correctly. I am attaching the file below my signature, try that:

        Column =
VAR CurrentCategory = Abu[Category]
VAR AllAlphabets =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 65, 90, 1 ),
            "Alphabets", LOWER ( UNICHAR ( [Value] ) )
        ),
        "Alphabets", [Alphabets]
    )
VAR RemovePeople =
    SUBSTITUTE ( CurrentCategory, " people", "" )
VAR SlashPostion =
    SEARCH ( "/", RemovePeople, 1, 0 )
VAR Result =
    IF (
        SlashPostion > 0,
        VAR LeftValue =
            MID ( RemovePeople, 1, SlashPostion - 1 )
        VAR RightValue =
            MID ( RemovePeople, SlashPostion + 1, LEN ( RemovePeople ) - SlashPostion )
        VAR Result =
            INT ( LeftValue ) + INT ( RightValue )
        RETURN
            Result,
        INT ( RemovePeople )
    )
RETURN
    Result