Forum Discussion
Helper IIRetrive text from other column in direct query
Hi everyone,
I need help about dax in directquery mode.
Basicly, I want to retrive value from this column covidSymptoms to new column, as follow:
CovidSymptoms
fever lossofsmell cough
cough fever
lossofsmell cough
fever lossofsmell
I want to give space and separator, and I hope the new column or costum table should be like this:
CovidSymptoms
fever, loss of smell, cough
cough, fever
lossofsmell, cough
fever, loss of smell
Stress about this, I try a lot of different ways such as using text.combines, text.contains with if statement but doesnt work in directquery mode. Your prompt response will be appriciated.
TaufikMaggangka , You might able to get complex logic work at the column level, You have to create a measure. You can use search or find or containsstring
Hi v-yalanwu-msft ,
Thank you so much. This is give me a clue.
but I have another issues since I also want replace value of "fever" to "Fever", cough to "Cough"(basically I want to first alfabet is Capital) not only loss of smell will be replace.
I try your sollustion, like this, but doesnt work. your future help will be appriciated.Measure = var _replace=SUBSTITUTE(MAX([CovidSymptoms])," ",",") return SUBSTITUTE(_replace,"lossofsmel","loss of smel") return SUBSTITUTE(_replace,"fever","Fever") return SUBSTITUTE(_replace,"cough","Cough")Hi, TaufikMaggangka ;
You could change measure as follows:
Measure = var _a=SUBSTITUTE(MAX([CovidSymptoms])," ",",") var _b=SUBSTITUTE(_a,"lossofsmel","loss of smel") var _c=SUBSTITUTE(_b,"fever","Fever") return SUBSTITUTE(_c,"cough","Cough")Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
- amitchandak
Super User
TaufikMaggangka , You might able to get complex logic work at the column level, You have to create a measure. You can use search or find or containsstring
TaufikMaggangka- Thanks amitchandak For your clue. Would you like to give me an exampel measure based on my table above. I try to create measure but not working since this also need to combine the the texts.
v-yalanwu-msftCommunity Support
Hi, TaufikMaggangka ;
If you want to achieve it in power query ,you could Right click and use Replace Values, such as:
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- TaufikMaggangka
Thank you for trying to help, but this is not working in "Direct Query mode"
- v-yalanwu-msft
Hi, TaufikMaggangka ;
If your .pbix is "Direct Query mode" , you should use dax (create a measure )as follows:
Measure = var _replace=SUBSTITUTE(MAX([CovidSymptoms])," ",",") return SUBSTITUTE(_replace,"lossofsmel","loss of smel")he final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- aj1973
Community Champion
It is good to know that there is some limitations in using DQ Mode.
Your model might fall into one of them...
Maybe a Measure with DAX can do the work to see it into a visual, but I don't think that adding a Calculated column is possible.
