Forum Discussion

FrankWoody's avatar
FrankWoody
Helper I
4 years ago
Solved

DAX CHALLENGE COLLUM

TEXTRESULT 
TICKET 3-XI-7-KOXI-7
TICKET 7-XI-10-EBGXI-10
TICKET 7-XI-11D-KOXI-11D
TICKET 4-XI-12-ERLXI-12
TICKET IC-1-IC-1
TICKET IC-6IC-6
TICKET 7-EU-226-SWEU-226
TICKET 7-FLK-86IN-GUFLK-86IN
GloveGlove
CHIEN CHIEN 
KLOW KLOW 
HH HH 

 

I'm trying to create a column to get the result by extracting information from the TEXT column.
But it's not returning exactly what I need:
NEW =
VAR _Len =
LEN (Sheet1[TEXT])
VAR _Dash_No =
_Len - LEN ( SUBSTITUTE ( Sheet1[TEXT], "-", "" ) )
VAR _C =
IF ( LEFT ( RIGHT ( Sheet1[TEXT], 4 ), 1 ) = "-", 4, 3 )
VAR _K =
IF ( _Dash_No > 2, RIGHT ( Sheet1[TEXT], _Len - 2 ), Sheet1[TEXT] )
VAR _Re =
IF ( _Dash_No > 2, LEFT ( _K, LEN ( _K ) - _C ), LEFT ( Sheet1[TEXT], 6 ) )
VAR DO=
SWITCH(
TRUE(),
CONTAINSSTRING(Sheet1[TEXT],"TICKET"),_Re,
Sheet1[TEXT]
)
RETURN
DO


  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi FrankWoody 
    Here is a sample file with the solution https://www.dropbox.com/t/TTpa5YQromfNolr1

     

    RESULT1 = 
VAR TextValue = Sheet1[TEXT]
VAR Length = LEN ( TextValue )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Letter1", MID ( TextValue, [Value], 1 ) )
VAR T3 = ADDCOLUMNS ( T2, "@Letter2", IF ( [@Letter1] = " " || [@Letter1] = "-", "|", [@Letter1] ) )
VAR NewText = CONCATENATEX ( T3, [@Letter2] )
VAR NumOfWords = PATHLENGTH ( NewText )
RETURN
    SWITCH ( 
        TRUE ( ),
        NumOfWords = 5, PATHITEM ( NewText, 3 ) & "-" & PATHITEM ( NewText, 4 ), 
        NumOfWords = 4 || NumOfWords = 3 || NumOfWords = 2, PATHITEM ( NewText, 2 ) & "-" & PATHITEM ( NewText, 3 ),
        TextValue
    )

     

1 Reply

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi FrankWoody 
    Here is a sample file with the solution https://www.dropbox.com/t/TTpa5YQromfNolr1

     

    RESULT1 = 
VAR TextValue = Sheet1[TEXT]
VAR Length = LEN ( TextValue )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Letter1", MID ( TextValue, [Value], 1 ) )
VAR T3 = ADDCOLUMNS ( T2, "@Letter2", IF ( [@Letter1] = " " || [@Letter1] = "-", "|", [@Letter1] ) )
VAR NewText = CONCATENATEX ( T3, [@Letter2] )
VAR NumOfWords = PATHLENGTH ( NewText )
RETURN
    SWITCH ( 
        TRUE ( ),
        NumOfWords = 5, PATHITEM ( NewText, 3 ) & "-" & PATHITEM ( NewText, 4 ), 
        NumOfWords = 4 || NumOfWords = 3 || NumOfWords = 2, PATHITEM ( NewText, 2 ) & "-" & PATHITEM ( NewText, 3 ),
        TextValue
    )