topic concatenate Text in direct query mode in DAX Commands and Tips

concatenate Text in direct query mode

TaufikMaggangka — Wed, 14 Jul 2021 13:36:04 GMT

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

Re: concatenate Text in direct query mode

Samarth_18 — Wed, 14 Jul 2021 14:28:38 GMT

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

Re: concatenate Text in direct query mode

TaufikMaggangka — Wed, 14 Jul 2021 15:26:45 GMT

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-DirectQuery/m-p/409398
which explain that "concatenatex" doesnt support in direct query mode.

Your future support will be really appreciated Samarth_18

Re: concatenate Text in direct query mode

Samarth_18 — Thu, 15 Jul 2021 08:50:15 GMT

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

Re: concatenate Text in direct query mode

TaufikMaggangka — Thu, 15 Jul 2021 09:12:05 GMT

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.