Forum Discussion
FrankWoody
Helper I
Helper ICHALLENGE 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...
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 _ReOutput:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn | Twitter | Blog | YouTube
FIFA World Cup - Medal Records
VahidDM
Super User
Super UserHi 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!!
LinkedIn | Twitter | Blog | YouTube
FIFA World Cup - Medal Records
FrankWoodyRESULT TEXT XI-7 TICKET 3-XI-7-KO XI-10 TICKET 7-XI-10-EBG XI-11D TICKET 7-XI-11D-KO XI-12 TICKET 4-XI-12-ERL IC-1 TICKET IC-1- IC-6 TICKET IC-6 EU-226 TICKET 7-EU-226-SW FLK-86IN TICKET 7-FLK-86IN-GU Glove CHIEN KLOW HH Is there any simpler way to get it straight from the text?