Forum Discussion
Extract values from string with DAX
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))
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))Thank you very much for your assistance, most appreciated. It is the right direction but the results are not as expected. Please find below actual results and expected results.
Dissolution
Column 1 (Actual)
Expected 1
Column 2 (Actual)
Expected 2
Column 3 (Actual)
Expected 3
Complies at S1
mean (n=6):
Lamivudine: 99.8%
Zidovudine: 101.0%
100.00%
99.80%
101.00%
Complies at S1
mean (n=6):
Nevirapine: 98.3%
98.70%
98.30%
100.10%
Complies at S1
mean (n=6):
Nevirapine: 100.2%
101.00%
100.20%
102.70%
Complies at S1
mean (n=6):
Nevirapine: 101.5%
100.70%
101.50%
100.70%
Complies at S1
mean (n=6):
Nevirapine: 100.5%
98.30%
100.50%
98.00%
Complies at S1
mean (n=6):
Lamivudine: 96.0%
Zidovudine: 95.3%
97.40%
96.00%
98.20%
95.30%
Complies at S1
mean (n=6):
Nevirapine: 100.2%
91.20%
100.20%
Complies at S1
mean (n=6):
Efavirenz: 98.7%
Emtricitabine: 100.5%
Tenofovir d.f.: 97.5%
95.30%
98.70%
94.90%
100.50%
97.50%
Complies at S1
mean (n=6):
Nevirapine: 98.0%
93.70%
98.00%
94.70%
Complies at S1
mean (n=6):
Efavirenz: 90.0%
Emtricitabine: 98.8%
Tenofovir d.f.: 92.5%
97.30%
90.00%
98.80%
98.80%
92.50%
Complies at S1
mean (n=6): 90.4%
103.20%
90.40%
Complies at S1
mean (n=6): 101.5%
111.10%
101.50%
Complies at S1
mean (n=6):
Nevirapine: 99.5%
98.60%
99.50%
99.00%
Complies at S1
mean (n=6):
Nevirapine: 98.8%
101.40%
98.80%
101.10%
Complies at S1
mean (n=6): 111.1%
89.20%
111.10%
88.30%
Complies at S1
mean (n=6): 103.2%
91.20%
103.20%
Complies at S1
mean (n=6): 97.3%
99.30%
97.30%
100.60%
Complies at S1
mean (n=6):
Lamivudine: 93.7%
Zidovudine: 94.7%
101.40%
93.70%
101.50%
94.70%
Complies at S1
mean (n=6):
Lamivudine: 95.3%
Zidovudine: 94.9%
101.30%
95.30%
101.10%
94.90%
Complies at S1
mean (n=6): 91.2%
92.50%
91.20%
Complies at S1
mean (n=6):
Lamivudine: 97.4%
Zidovudine: 98.2%
104.00%
97.40%
98.20%
Complies at S1
mean (n=6):
Lamivudine: 98.3%
Zidovudine: 98.0%
101.00%
98.30%
102.00%
98.00%
Complies at S1
mean (n=6):
Lamivudine: 100.7%
Zidovudine: 100.7%
99.10%
100.70%
101.00%
100.70%
Complies at S1
mean (n=6):
Lamivudine: 101.0%
Zidovudine: 102.7%
100.20%
101.00%
101.40%
102.70%
Complies at S1
mean (n=6):
Lamivudine: 95.2%
Zidovudine: 103.6%
100.30%
95.20%
100.30%
103.60%
Complies at S1
mean (n=6):
Lamivudine: 96.3%
Zidovudine: 100.5%
98.70%
96.30%
101.20%
100.50%
Complies at S1
mean (n=6):
Lamivudine: 96.8%
Zidovudine: 102.7%
96.70%
96.80%
102.70%
Complies at S1
mean (n=6):
Lamivudine: 99.8%
Zidovudine: 101.4%
97.70%
99.80%
101.40%
Complies at S1
mean (n=6):
Lamivudine: 100.1%
Zidovudine: 101.5%
96.90%
100.10%
98.60%
101.50%
98.40%
Complies at S1
mean (n=6):
Lamivudine: 99.2%
Zidovudine: 100.8%
96.20%
99.20%
99.70%
100.80%
97.90%
Best regards,
Chris
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.
Many thanks, managed to resolve the problem.
Regards,
Chris
