Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Get value from text after delimiter

Hi, I wanna extract first number 144272(after second -) and third number 19469(after fourth -). Desired output is 114272-19469   Delimiter structure will be same but number string length are dyna...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • v-saisrao-msft's avatar
    v-saisrao-msft
    1 year ago

    Hi Anonymous,

    Here is the DAX measure I used, which produced the desired output. Please review the attached PBIX file for your reference.

    ExtractedCode = 
VAR FullText = [Column1]
VAR FirstSlash = SEARCH("/", FullText, 1)
VAR SecondSlash = SEARCH("/", FullText, FirstSlash + 1)
VAR ThirdSlash = SEARCH("/", FullText, SecondSlash + 1)

VAR Text1 = MID(FullText, FirstSlash + 1, SecondSlash - FirstSlash - 1)
VAR Text2 = MID(FullText, SecondSlash + 1, ThirdSlash - SecondSlash - 1)

VAR Digits = "0123456789"

VAR OnlyNums1 = CONCATENATEX(
    ADDCOLUMNS( 
        GENERATESERIES(1, LEN(Text1)), 
        "Char", MID(Text1, [Value], 1)
    ),
    IF(CONTAINSSTRING(Digits, [Char]), [Char], ""),
    ""
)

VAR OnlyNums2 = CONCATENATEX(
    ADDCOLUMNS( 
        GENERATESERIES(1, LEN(Text2)), 
        "Char", MID(Text2, [Value], 1)
    ),
    IF(CONTAINSSTRING(Digits, [Char]), [Char], ""),
    ""
)

RETURN OnlyNums1 & "-" & OnlyNums2

     

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

     

    Thank you.

ajaybabuinturi's avatar
ajaybabuinturi
Icon for Super User rankSuper User
1 year ago

Hi Anonymous,
I would suggest to create a calculated column with below DAX logic to get desire out put. 

ExtractedValue = 
VAR FullText = 'TestTable'[Column1]
VAR FirstDash2 = SEARCH("-", FullText, SEARCH("-", FullText) + 1)
VAR SlashAfterSecond = SEARCH("/", FullText, FirstDash2)
VAR FirstNumber = MID(FullText, FirstDash2 + 1, SlashAfterSecond - FirstDash2 - 1)

VAR FourthDash = SEARCH("-", FullText, SEARCH("-", FullText, SEARCH("-", FullText, SEARCH("-", FullText) + 1) + 1) + 1)
VAR SlashAfterFourth = SEARCH("/", FullText, FourthDash)
VAR ThirdNumber = MID(FullText, FourthDash + 1, SlashAfterFourth - FourthDash - 1)

VAR Result = FirstNumber & "-" & ThirdNumber

RETURN
Result

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.



  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    I've got error,,

    actually I have first number(DU ID) from other column already so I used to use this. 

     

    FDD Mapping =
        'LNCEL_FDD'[DU ID] & "-" &
            VAR _right = right([$dn], len([$dn]) - search("-", [$dn], 35, 0))
            RETURN LEFT(_right, SEARCH("/", _right) -1)
     
    but the same error message pops up as below and I don't know why. 
     

     

     

    • ajaybabuinturi's avatar
      ajaybabuinturi
      Icon for Super User rankSuper User
      1 year ago

      Anonymous Could you please provide some sample data of .pbix file, so that it is easy to understand your requirement and get the expected results.

      Thanks,