Concat two columns(distinct) and display as a SECTION

[Archer]

Comments Data.xlsx (13.1 KB)

Hi,
I am working on Direct Query Mode where I need to show Survey Origin & Survey Responses in One Column and display 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
Thanks
Archer

[Archer]

So far i’ve got below DAX 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)

[AntrikshSharma]

@Archer Try this: Archer.pbix (16.7 KB)

Result =
VAR CurrentOrigin =
    SELECTEDVALUE ( Archer[Survey Origin] )
VAR FilterTable =
    FILTER ( ALL ( Archer ), Archer[Survey Origin] = CurrentOrigin )
VAR ConcatenateComments =
    CONCATENATEX ( FilterTable, Archer[Responses], UNICHAR ( 10 ) )
RETURN
    ConcatenateComments

[Archer]

Thats it ! thanks a lot
I have tweaked it to match my dataset/data model since it is Direct Query but boy this was bingo … Much Appreciated