Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Split column by delimeter in dax

Input and expected output

Hi ,

I need to split the input column by demieter to display the last character before the occurance of "_" . If there is no "_" then it must display the character itself. So i just need to dispaly the last character after an "_". I need to do this in DAX as i am using Direct Query as my mode of import.Please refer the picture above for your reference.

 

Thanks

d
  • ValtteriN's avatar
    ValtteriN
    3 years ago

    Hi,

    Here is one way to do this:

    Data:

     

    Dax:

    Measure 24 =
    var _text = MAX('Table (13)'[Column1]) return
    RIGHT(_text,LEN(_text)-SEARCH("_",_text,,0))
    End result:
     

    Edit:

    I noticed this doesn't account for second instance of "_". For that use this dax:

    Measure 24 =
    var _text = MAX('Table (13)'[Column1])
     var first_instance = FIND("_",_text,,0)
     var second_instance = if(FIND("_",_text,first_instance+1,0)=0,first_instance,FIND("_",_text,first_instance+1,0))
     return
    RIGHT(_text,LEN(_text)-IF(first_instance>0,second_instance))



    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

4 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    3 years ago

    Hi,

    Here is one way to do this:

    Data:

     

    Dax:

    Measure 24 =
    var _text = MAX('Table (13)'[Column1]) return
    RIGHT(_text,LEN(_text)-SEARCH("_",_text,,0))
    End result:
     

    Edit:

    I noticed this doesn't account for second instance of "_". For that use this dax:

    Measure 24 =
    var _text = MAX('Table (13)'[Column1])
     var first_instance = FIND("_",_text,,0)
     var second_instance = if(FIND("_",_text,first_instance+1,0)=0,first_instance,FIND("_",_text,first_instance+1,0))
     return
    RIGHT(_text,LEN(_text)-IF(first_instance>0,second_instance))



    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ValtteriN 
    There seems to be an error with this dax as using max will take only value. For example can you add data other than test and check it wont show. data in_imp ,test_dmp .output: imp,dmp. This logic works only when using same word after underscore"_".

    Can you check and let me know

    • ValtteriN's avatar
      ValtteriN
      Community Champion
      3 years ago

      Hi,

      Here is an example with other data:

       

      End result:

       

      The MAX here works similarly as SELECTEDVALUE.