Forum Discussion

FrankWoody's avatar
FrankWoody
Icon for Helper I rankHelper I
4 years ago
Solved

CHALLENGE CALCULATED COLUMN

HELLO, DEEARSS   I have this table : DATE RESULT 3-TDQ-44-EUA TDQ-44 3-TDQ-55-EUA  TDQ-55 7-TUQ-08-EUA TUQ-08 7-ITQ-100-NW ITQ-100 1-XI-3-KO XI-3 3-XI-4-LOG XI-4 3-XI...
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi FrankWoody 

     

    Try this DAX expression to add your new calculated column:

    New Column = 
VAR _Len =
    LEN ( 'Table'[DATE] )
VAR _Dash_No =
    _Len - LEN ( SUBSTITUTE ( 'Table'[DATE], "-", "" ) )
VAR _C =
    IF ( LEFT ( RIGHT ( 'Table'[DATE], 4 ), 1 ) = "-", 4, 3 )
VAR _K =
    IF ( _Dash_No > 2, RIGHT ( 'Table'[DATE], _Len - 2 ), 'Table'[DATE] )
VAR _Re =
    IF ( _Dash_No > 2, LEFT ( _K, LEN ( _K ) - _C ), LEFT ( 'Table'[DATE], 4 ) )
RETURN
    _Re

     

    Output:

     



     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

    Appreciate your Kudos!! 

    All logo

    LinkedIn | Twitter | Blog | YouTube 

    FIFA World Cup - Medal Records 

Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

Hi:

You can try this calculatedcolmun:

New Column =
VAR Firstdash =
FIND (
"-",
'Table'[Date],
1
)
VAR SecondDash =
FIND (
"-",
'Table'[Date],
FirstDash + 1
)
VAR thirdDash =
FIND (
"-",
'Table'[Date],
SecondDash + 2
)

RETURN
MID (
'Table'[Date],
FirstDash+1 ,
ThirdDash-3 )
I hope this is a good solution for you.

 

FrankWoody's avatar
FrankWoody
Icon for Helper I rankHelper I
4 years ago

I tried to use FIND but this error appears "Could not find the Search text given to function 'FIND' in the text in question."

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi:

    That is strange. Please see attached solution. The message is saying what ever you have between the " " it couldn't find. I copied your data and it's the dash it is finding. "-" . If you put an extra space or did not use - then it can't find it. Can you try again as the DAX is the most easy to understand for future applications for you and community.

    Please mark as solution if you may having been putting an extra space between "-". Or using _ instead of -. I hope this solution makes sense. Thanks

    https://drive.google.com/file/d/1xg_jdINqdl9DP-77vKfHKGa2SBnJNXYF/view?usp=sharing 

    New Column =
    VAR Firstdash =
    FIND (
    "-",
    'Table'[Date],
    1
    )
    VAR SecondDash =
    FIND (
    "-",
    'Table'[Date],
    FirstDash + 1
    )
    VAR thirdDash =
    FIND (
    "-",
    'Table'[Date],
    SecondDash + 2
    )

    RETURN
    MID (
    'Table'[Date],
    FirstDash+1 ,
    ThirdDash-3 )