Forum Discussion

Chitemerere's avatar
Chitemerere
Responsive Resident
6 years ago
Solved

Extract values from string with DAX

I have an attribute (column) called "Dissolution" from a table in Power BI which is a string with values in the string. I want to extract the values as new attributes (columns).  The sample column is...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Chitemerere - That's pretty ugly but I believe these will work:

     

    Column 1 = 
    VAR __Pos = SEARCH("%",[Dissolution],,-1)
    VAR __Col = IF(__Pos=-1,-1,SEARCH(":",[Dissolution],__Pos - 8,-1))
RETURN
    IF(__Pos = -1 || __Col = -1,BLANK(),MID([Dissolution],__Col+2, __Pos-__Col-1))


Column 2 = 
    VAR __Pos1 = SEARCH("%",[Dissolution],,-1)
    VAR __Pos = SEARCH("%",[Dissolution],__Pos1 + 1,-1)
    VAR __Col = IF(__Pos=-1,-1,SEARCH(":",[Dissolution],__Pos - 8,-1))
RETURN
    IF(__Pos = -1 || __Col = -1,BLANK(),MID([Dissolution],__Col+2, __Pos-__Col-1))


Column 3 = 
    VAR __Pos1 = SEARCH("%",[Dissolution],,-1)
    VAR __Pos2 = SEARCH("%",[Dissolution],__Pos1 + 1,-1)
    VAR __Pos = IF(__Pos2 = -1,-1,SEARCH("%",[Dissolution],__Pos2 + 1,-1))
    VAR __Col = IF(__Pos=-1,-1,SEARCH(":",[Dissolution],__Pos - 8,-1))
RETURN
    IF(__Pos = -1 || __Col = -1,BLANK(),MID([Dissolution],__Col+2, __Pos-__Col-1))
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Chitemerere - That's pretty ugly but I believe these will work:

 

Column 1 = 
    VAR __Pos = SEARCH("%",[Dissolution],,-1)
    VAR __Col = IF(__Pos=-1,-1,SEARCH(":",[Dissolution],__Pos - 8,-1))
RETURN
    IF(__Pos = -1 || __Col = -1,BLANK(),MID([Dissolution],__Col+2, __Pos-__Col-1))


Column 2 = 
    VAR __Pos1 = SEARCH("%",[Dissolution],,-1)
    VAR __Pos = SEARCH("%",[Dissolution],__Pos1 + 1,-1)
    VAR __Col = IF(__Pos=-1,-1,SEARCH(":",[Dissolution],__Pos - 8,-1))
RETURN
    IF(__Pos = -1 || __Col = -1,BLANK(),MID([Dissolution],__Col+2, __Pos-__Col-1))


Column 3 = 
    VAR __Pos1 = SEARCH("%",[Dissolution],,-1)
    VAR __Pos2 = SEARCH("%",[Dissolution],__Pos1 + 1,-1)
    VAR __Pos = IF(__Pos2 = -1,-1,SEARCH("%",[Dissolution],__Pos2 + 1,-1))
    VAR __Col = IF(__Pos=-1,-1,SEARCH(":",[Dissolution],__Pos - 8,-1))
RETURN
    IF(__Pos = -1 || __Col = -1,BLANK(),MID([Dissolution],__Col+2, __Pos-__Col-1))