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

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

Community Champion

6 years ago

Chitemerere - It's hard to say, it looks like it is inventing numbers that aren't actually in the row/column so no idea how that could be happening. You are implementing this as a column, correct? Not a measure?

Like your first row,

Complies at S1

mean (n=6):

Lamivudine: 99.8%

Zidovudine: 101.0%

I don't see where the calculation could possibly get 100.00% That does not even seem possible to me because that number isn't even in the text anywhere. I'm attaching my working PBIX. You want Table (2). It is based on your original sample data.

working61.pbix30 KB