concatenate Text in direct query mode

[TaufikMaggangka]

Hi all,

I need help from expert about concenate text in direct query mode.

I need to create a new column based on several conditions or if statements into new column. Here the illustration of the table that I have, I named it "staging Screening"

Fever	SkinChanges	Lethargy	DryMounth
5	true	false	true
6	true	false	true
3	false	true	true
2	true	false	false
2	false	true	false

note for if statements:
If "fever"column more than 5, the result text will be "Fever more than 5 days"
if "skinchanges" is "is true, then result text will be "Rash on student skin"
if "lethargy" is "true", then text will be "Very sleepy"
if "drymounth" is "true", the text will be"Mouth very dry"

I hope the result of new column below, if using case above will be like this:

Fever	SkinChanges	Lethargy	DryMounth	newcolumn
5	true	false	true	Fever more than 5 days, Rash on student skin, Mouth very dry
6	true	false	true	Fever more than 5 days, Rash on student skin, Mouth very dry
3	false	true	true	Very sleepy,Mouth very dry
2	true	false	false	Rash on student skin
2	false	true	false	Very sleepy

Please also note that i am working with power query mode, I already try text combine function but not allowed if the statement more than 5. 

I also try with combine value function but there are delimiter if the result is blank: here the dax that I used and result :

any help from expert will be really appreciated.

Thank you

[Samarth_18]

Hi @TaufikMaggangka ,

You can create a column with below code:-

Column = 
var fevercheck = IF('Table (8)'[Fever] > 5,"Fever more than 5 days",BLANK())
var skincheck = IF('Table (8)'[SkinChanges] = TRUE(),"Rash on student skin",BLANK())
var lethrgycheck = IF('Table (8)'[Lethargy] = TRUE(),"Very sleepy",BLANK())
var drymounthcheck = IF('Table (8)'[DryMounth] = TRUE(),"Mouth very dry",BLANK())
var listsymptom = {fevercheck,skincheck,lethrgycheck,drymounthcheck}
RETURN
  CONCATENATEX(
      FILTER(listsymptom,[Value]<>""),
      [Value],
      ","
  )

Thanks

[TaufikMaggangka]

HI @Samarth_18 , 

Thank you for trying to help. 
But in any reason "concatenatex" not working or cannot be found in "direct query mode". Please see my screenshoot below. I also read here : https://community.powerbi.com/t5/Report-Server/DAX-Function-CONCATENATEX-is-not-supported-on-PBIRS-D...
which explain that  "concatenatex" doesnt support in direct query mode.

Your future support will be really appreciated @Samarth_18 

[Samarth_18]

Hi @TaufikMaggangka 

You can create a custom column with below code in transform data window.

= let 
fevercheck = if [Fever] > 5 then "Fever more than 5 days" else null,
skincheck = if [SkinChanges] = true then "Rash on student skin" else null,
lethrgycheck = if[Lethargy] = true then "Very sleepy" else null,
drymounthcheck = if[DryMounth] = true then "Mouth very dry" else null,

symptomslist = Text.Combine({fevercheck,skincheck,lethrgycheck,drymounthcheck},",")

in symptomslist

Thanks

[TaufikMaggangka]

Hi @Samarth_18 ,

Thank you again trying to help,

I have tried your suggestion but This query contains transformations that can't be used for DirectQuery. Its why I try to add column or measure to fix this.

Do you have any other suggestion to solve this ? I have try with new column but there delimiter show if the result is blank, Do you have any suggestion about this ? how to ingore blank in Combine value function. Please see atatched above to see my syntax.

Your future help will be really appreciated, stress about this.