Concat two columns(distinct) and display as a SECTION

[archerjayden]

Hi,

I am working on Direct Query Mode where I need to show Survey Origin & Survey Responses in One Column and display them as Section.

Please find attached raw data and desired result.

I am stuck on how to achieve this in DAX and want to get expert’s resolution and views on this scenario.

Please note that Survey Origin is a concatenate field of Region & Country etc., Survey Responses are Answers to Open Questions provided.

They are both coming in a single view from Sql Server Please guide on how best I can solve this with DAX

Raw Data

Survey Origin	Responses
APAC-Australia	Moving from face-to-face connections to all virtual, but embracing the challenge ensured it was successful.
APAC-Australia	Stock shortages Must have a better understanding of supply chain ie where plants are located, how many
APAC-Australia	Supplier relationship management. Communication super important during challenging times.
APAC-China	Hospital tender stops during Covid-19 and many market access work have been delayed
APAC-China	Optimize inventory
APAC-China	Planned storage for medical equipment and cunsumbles.
APAC-India	Price Stability has to be there even in difficult times,
APAC-India	Face to face contact to avoid covid …newer technologies to discuss and negotiation
APAC-India	Unexpected situation. Surge in bed demand. Logistic disruption. Associate with partners with credibility to cover unexpected surge. medical supplies.
APAC-Japan	Increased awareness for infection prevention. Held on-line meeting and it was well received. Due to increase of web seminar, it becomes easier to participate without transfer. I was in trouble my patient couldn’t be hospitalized because we couldn’t find the hospital. The number of home care patient has increased. Patients are reluctant to be hospitalized due to fear of COVID-19. If patient have a fever, we couldn’t start treatment until PCR test results are negative, which was inefficient. Also, my patient was refused to be hospitalized due to fever

Desired Result

Section

APAC-Australia :- Moving from face-to-face connections to all virtual, but embracing the challenge ensured it was successful. Stock shortages Must have a better understanding of supply chain ie where plants are located, how many Supplier relationship management. Communication super important during challenging times.

APAC-China :- Hospital tender stops during Covid-19 and many market access work have been delayed Optimize inventory Planned storage for medical equipment and cunsumbles.

APAC-India Price Stability has to be there even in difficult times, Face to face contact to avoid covid …newer technologies to discuss and negotiation Unexpected situation. Surge in bed demand. Logistic disruption. Associate with partners with credibility to cover unexpected surge. medical supplies.

APAC-Japan Increased awareness for infection prevention. Held on-line meeting and it was well received. Due to increase of web seminar, it becomes easier to participate without transfer. I was in trouble my patient couldn’t be hospitalized because we couldn’t find the hospital. The number of home care patient has increased. Patients are reluctant to be hospitalized due to fear of COVID-19. If patient have a fever, we couldn’t start treatment until PCR test results are negative, which was inefficient. Also, my patient was refused to be hospitalized due to fever

DAX Code I've got so far which doesnt work 

Section Measure =
VAR Region =DISTINCT(surveytable[region_name])
VAR Responses = CALCULATE(
CONCATENATEX(
VALUES(surveytable),
surveytable[comments],
UNICHAR( 10 ),
surveytable[region_name],ASC),
FILTER(
surveytable,
surveytable[comments] <> BLANK()))
RETURN
CONCATENATE(Region,Responses)

Thanks,

Archer

[AntrikshSharma]

@archerjayden Try this:

The file is attached below my signature.

If you want to show comments only for the origins selected in a slicer, then just replace ALL with ALLSELECTED. 

[CNENFRNL]

Hi, @archerjayden , I'm wondering why you left Power Query behind in such a senario; you know, PQ is a more competent tool to cope with such requirements. It gets job done with literally one line of M code.

You might want to try it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVRBbiM3EPxKQ2dJQI45CloE6yCLNeIkl10fOJweTcMcckI2NVFO+418IA/bl6SaIyUynOQiS2azq7q6ip8+bQ6Ph+PuUItmF8RttpsP6SzxRENOEw3O807Tzv6STzGyV0mxkCZyIdBZslYXttRVJZ667Lzd1RHVIwo4npg4lpq5J1FaXKFSvedShhr2m+ftvxB40uRfqIwpqztx+Zw/xw84ptGdmRx1rMqZauw5F3WxN8Q0oO88h4vhSiRhWkbOTHNwUQs5fA3JO+V+S2NaaHLx8h/w1keAkDm4Nu0os9WDzMRR93RM01Sj+HZquCiWaTa+Uamv2Rjd5m96yMTlbtrjKNGg3qcyi7pAyjYNFU1zuTU4Yg/97ptvCSM2uvjIL6zkmn60pPyyatIxR+rB9sL9W5CPM+DldyaJZ9BP+fK25hEqRawIBDLGpCFlmrjHiIH41yqzDd6I+IptTl14Nc8DltDaZIFPntR1EkQvYNec0rE5AhtgEAAN6mUYxNegqzLbt52+M8Ph6s146rw2052TgIRJQ1+//Bl5gWzKfowppJNww+ul+AqFjG/kU1Jpm3qL8nPk32ZY2iYXGNmq9vRU88nUAu8eskL6fk8/oHtR8dY817lVwpnw5qGU5IEAx4mONLusEc5cf3kYX65qgBmIN+v+A2tY+7+1Lqv37rX93s0ugutDRC9XcMctcHM0C9iaJA5rKGnOpu86gxF7z6GnFHdBIgOB1VxlmlyDuDASnNmznLlfr7yrTXW5YlmsFu6o8ASf5K3d7Ngn7IxwbiFBtU0sXuabBAlvAdIUy8DZNIoPDQ6Cak4V1qHpgksq5imfaujj1y9/WGckcw0E7NobkqsgsfB91SCYwB6YW+3K/Cf8J8KYoATSIyiSt9DfcMbG4CrheuVxPVofB4S9+pbf1bCvmPSrLAO71v348ZeHd0jmOtxwh9EeqAFrgFavaeOlyiYLO21ZqlhUoMfjj3Av3rbMBXFYqcCyaHhmtBjFj1fx2CIj7f05hJK29yJaReahmjv+jz54bZ6f/wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Survey Origin" = _t, Responses = _t]),
    
    //Simply one single line to get the job done
    #"Grouped Rows" = Table.Group(Source, {"Survey Origin"}, {{"All Responses", each Text.Combine([Responses],"#(tab)"), type text}})
in
    #"Grouped Rows"