Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Concatenate string from relating rows

Dear All, I have a use case where I would like to create description strings for a travel company about road trips.   Summary table: Road trip ID Description string Travel1 Milano-Geneva-...
Need Help
  • camargos88's avatar
    camargos88
    5 years ago

    Anonymous ,

     

    Check this new code:

    Summary = 
    ADDCOLUMNS(
        VALUES('Table'[Road trip ID]), 
        "Desc", CONCATENATEX(FILTER('Table', 'Table'[Road trip ID] = EARLIER('Table'[Road trip ID])), 'Table'[Stop place], "-", 'Table'[Stop index], ASC) 
    )

     

    Concatenex has a sort by parameter.

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

Hi Anonymous 

I assume you have the summary table in place with the Road trip ID column. If so:

Description string =
CONCATENATEX (
    CALCULATETABLE (
        DISTINCT ( RawTable[Stop place] ),
        FILTER (
            ALL ( RawTable[Road Trip ID] ),
            RawTable[Road Trip ID] = SummaryTable[Road Trip ID]
        )
    ),
    RawTable[Stop place],
    "-"
)

This can also be done in Power query, and it would be probably better.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers